Oracle and PL/SQL Recipes: A Problem-Solution Approach

8-3. Writing a Dynamic INSERT Statement

Problem

Your application must insert data into a table, but you don't know until runtime which columns you will insert. For example, you are writing a procedure that will be used for saving records into the EMPLOYEES table. However, the exact content to be saved is not known until runtime because the person who is calling the procedure can decide whether they are including a DEPARTMENT_ID. If a DEPARTMENT_ID is included, then the department will be included in the INSERT.

Solution

Create a string at runtime that will contain the INSERT statement text to be executed. Use bind variables to substitute the values that are to be inserted into the database table. The following procedure accepts user input for ...