O'Reilly logo

Oracle and PL/SQL Recipes: A Problem-Solution Approach by Matt Arena, Josh Juneau

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required