This content has been marked as final. Show 27 replies
declare cursor c_t is select table_name from user_tables where ... order by table_name; szSql varchar2(2048); begin for rec in c_t loop szSql := 'alter table '||rec.table_name||' add (your_column varchar2(100))'; dbms_output.put_line(szSql); execute immediate szSql; end loop; end; /
iam not sure but, try this code....if the stmt in loop shows an error then use "execute immediate" before the stmt
for i in select table_name from user_tables loop alter table i.table_name add column...... end loop
Edited by: kiran on Oct 14, 2010 3:54 PM
Thanks guys, I will try both and report back, right now preparing the list of my tables that I want to alter.
What good GUI tool exists for de-bugging ? Not proficient in SQL*Plus command line yet. Downloading SQL Developer, guess we'll see here soon how that works.
So this is a PL/SQL block right ? SQL Developer don't seem to be able to debug this just Functions and Procedures
What I dont get is where do I list the Tables that I want to alter ? in the CODE ?
If you want to practice PL/SQL then this is the wrong task.
You would need to use dynamic SQL (execute immediate or dbms_sql) for this, but usually you should avoid dynamic SQL for many reasons.
As you already mentioned this can be easily done with SQL.
What I do in such cases is to build a select that returns me the 10+ alter table statements.
The oracle data dictionary (e.g. the view user_tables) has enough information to build such a select based on your requirements.
Then save the result into a file and run the file as a script in sql*plus.
Changes to the data structure in your database should not be done programatically (e.g by pl/sql) but should be entered into your version control system so that you can run the same script against your development database, against your test database and later against your production database.
select 'alter table '||t.table_name||' add KEY varchar2(100);' from user_tables t where t.table_name like 'XYZ%' and t.table_name not in (select c.table_name from user_tab_columns c where column_name = 'KEY');
Hmmm, seems like a lot to type to alter a table and add a column, might as well do this using regular SQL.
So much for PL in SQL.
So much for PL in SQL.So much for doing DDL in pl/sql.
I guess, I should be able to declare a array as a list, list all the tables that I want to alter and loop.
Restrictions everywhere! :- )
Most documentation on this subject from oracle is cryptic, collections, arrays, no good examples.
You were shown a few good examples of doing what you wanted.
Didn't those work?
No need for arrays and all that. You're over-complicating things.
no, those don't work, it makes no sense to re-run this PL/SQL 20 times for 20 tables, since I have all the names of the 20 tables, I wanted to list them into a variable like maybe:
list_of_tables varchar2(1000) := 'table1,table2,table3,table4,.......'
and then loop those, anyways, that's what I will try next... The tables are in test mode, so if I screw up, no big deal, they are all backed up.
I get lots of errors from the examples above.
If you want to hard-code a list of table names, rather than having some criteria to select the names from the data dictionary, you can do something like
SQL> declare 2 type table_name_arr is table of varchar2(30); 3 table_names table_name_arr := table_name_arr( 'EMP', 'DEPT' ); 4 begin 5 for i in 1..table_names.count 6 loop 7 execute immediate 'ALTER TABLE ' || table_names(i) || 8 ' ADD( key varchar2(100) )'; 9 end loop; 10 end; 11 / PL/SQL procedure successfully completed.
Justin, let me try that, thank you!