Updating several tables and columns

[peterswan]

Hello,

I'm fairly new to Oracle PL/SQL and I've been assigned a fairly big task which I can accomplish the tedious way, but I'm wondering if someone could enlighten me towards a quicker solution to my assignment.

What I need to do is update 60 tables by adding new columns, then inserting IDs into these new columns by selecting the numeric ID from the employee table that correlates to the alpha-numeric ID that's already in both the emp table and the new tables. Here's a sample of the script I will run for one table:

ALTER TABLE csq_owner.csq_admin_impact ADD adm_imp_entered_by2 NUMBER;

UPDATE csq_admin_impact impact
SET adm_imp_entered_by2 = (
SELECT bemsid
FROM csq_employees emp
WHERE impact.adm_imp_entered_by = emp.employeeid);

ALTER TABLE csq_owner.csq_admin_impact DROP COLUMN adm_imp_entered_by;
ALTER TABLE csq_owner.csq_admin_impact RENAME COLUMN adm_imp_entered_by2 TO adm_imp_entered_by;

This will work fine for one table. However, what I'd like to do is run a script that will loop through a list of the tables and their associated columns, and create the new column, update that column, delete the old column, and rename the new column, and then go onto the next table, column, etc.

Can this be done? I'll need an array or something for the table names and the column names, and loop through the array. The employee table with the employeeid will be used for the update in every case.

Thanks,

Peter

 

[LKBrwnDBA]

1) Put the list of affected tables into a temp table.

2) Loop through this tamp table and use dynamic SQL to build and execute the ALTER and UPDATE statements.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[peterswan]

Is there any way you could provide an example of a script?

I'm not sure how to load these values into a temp table. Also, how would the dynamic SQL look?

Thanks,

Peter

 

[LKBrwnDBA]

Look into the EXECUTE IMMEDIATE statement.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb