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

8-10. Finding All Tables That Include a Specific Column Value

Problem

You are required to update all instances of a particular data column value across multiple tables within your database.

Solution

Search all user tables for the particular column you are interested in finding. Create a cursor that will be used to loop through all the results and execute a subsequent UPDATE statement in each iteration of the loop. The UPDATE statement will update all matching column values for the table that is current for that iteration of the cursor.

The following example shows how this technique can be performed. The procedure will be used to change a manager ID when a department or job position changes management.

CREATE OR REPLACE PROCEDURE change_manager(current_manager_id ...