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

8-5. Writing a Dynamic Delete Statement

Problem

You need to create a procedure that will delete rows from a table. However, the exact SQL for deleting the rows is not known until runtime. For instance, you need create a procedure to delete an employee from the EMPLOYEES table, but rather than limit the procedure to accepting only employee ID numbers for employee identification, you also want to accept an e-mail address. The procedure will determine whether an e-mail address or an ID has been passed and will construct the appropriate DELETE statement.

Solution

Use native dynamic SQL to process a string that is dynamically created based upon values that are passed into the procedure. In the following example, a procedure is created that will build ...