On Nov 1, 12:26 am, "faizal mangattil`" <faizmangat...@gmail.com> wrote: > i want to rename column name from set of tables. > > for ex: > > Begin > For Rec in (Select table_name from dba_tables where table_name like > '%_AUD' AND OWNER='AUD') > Loop > ALTER TABLE *REC.TABLE_NAME * > RENAME COLUMN AUD_MODIFIED_TIME TO MODIFIED_TIME; > End Loop; > End; > > Please share your logic > regards > faizal
You can't do what you want the way you have it coded; this example should show you how this is done: SQL> describe yappo Name Null? Type ----------------------------------------- -------- ---------------------------- SMORT NUMBER ALBEISTER VARCHAR2(20) COLNAME DATE SQL> describe blue_yappo Name Null? Type ----------------------------------------- -------- ---------------------------- SMORT NUMBER ALBEISTER VARCHAR2(20) COLNAME DATE SQL> describe red_yappo Name Null? Type ----------------------------------------- -------- ---------------------------- SMORT NUMBER ALBEISTER VARCHAR2(20) COLNAME DATE SQL> SQL> declare 2 sqltxt varchar2(2000); 3 begin 4 for rec in (select table_name from user_tables where table_name like '%YAPPO') loop 5 sqltxt := 'alter table '||rec.table_name||' rename column colname to yertz'; 6 execute immediate sqltxt; 7 end loop; 8 9 end; 10 / PL/SQL procedure successfully completed. SQL> SQL> describe yappo Name Null? Type ----------------------------------------- -------- ---------------------------- SMORT NUMBER ALBEISTER VARCHAR2(20) YERTZ DATE SQL> describe blue_yappo Name Null? Type ----------------------------------------- -------- ---------------------------- SMORT NUMBER ALBEISTER VARCHAR2(20) YERTZ DATE SQL> describe red_yappo Name Null? Type ----------------------------------------- -------- ---------------------------- SMORT NUMBER ALBEISTER VARCHAR2(20) YERTZ DATE SQL> "1. You may not be able to use dynamic view in pl sql. Use your own table containing the required table names. " Nonsense, I used the USER_TABLES view without issue in my example. There is a case where that may be true however it's not because it's a dynamic view, it would be due to not having a direct grant on the object and attempting to use it in a procedure, package or function compiled with authid owner rather than authid current_user. "2. Use "execute immediate" from pl sql to alter the table. Execute immediate ' alter table XYZ rename column ABC to DEF' ; " I did, as it's necessary, however it also requires building a string dynamically to insert the proper table name. The example from a prior post would rename the column in a single table then error out for all other occurrences: SQL> describe yuppo Name Null? Type ----------------------------------------- -------- ---------------------------- SMORT NUMBER ALBEISTER VARCHAR2(20) COLNAME DATE SQL> describe blue_yuppo Name Null? Type ----------------------------------------- -------- ---------------------------- SMORT NUMBER ALBEISTER VARCHAR2(20) COLNAME DATE SQL> describe red_yuppo Name Null? Type ----------------------------------------- -------- ---------------------------- SMORT NUMBER ALBEISTER VARCHAR2(20) COLNAME DATE SQL> SQL> declare 2 sqltxt varchar2(2000); 3 begin 4 for rec in (select table_name from user_tables where table_name like '%YUPPO') loop 5 sqltxt := 'alter table yuppo rename column colname to yertz'; 6 execute immediate sqltxt; 7 end loop; 8 9 end; 10 / declare * ERROR at line 1: ORA-00957: duplicate column name ORA-06512: at line 6 SQL> SQL> describe yuppo Name Null? Type ----------------------------------------- -------- ---------------------------- SMORT NUMBER ALBEISTER VARCHAR2(20) YERTZ DATE SQL> describe blue_yuppo Name Null? Type ----------------------------------------- -------- ---------------------------- SMORT NUMBER ALBEISTER VARCHAR2(20) COLNAME DATE SQL> describe red_yuppo Name Null? Type ----------------------------------------- -------- ---------------------------- SMORT NUMBER ALBEISTER VARCHAR2(20) COLNAME DATE SQL> David Fitzjarrell -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
