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

8-4. Writing a Dynamic Update Statement

Problem

Your application needs to execute an update statement, and you are not sure of the columns to be updated until runtime. For example, your application will modify employee records. You would like to construct an update statement that contains only the columns that have updated values.

Solution

Use native dynamic SQL to execute a SQL statement string that you prepare at application runtime. The procedure in this example accepts employee record values as input. In this scenario, an application form allows user entry for many of the fields that are contained within the EMPLOYEES table so that a particular employee record can be updated. The values that are changed on the form should be included in the ...