You write nonsense and you also agree with the nonsense to some level. By default,you can not use dynamic views in pl sql you need explicit privilege on them.
Regards, Jignesh Makwana On Nov 1, 2011 8:58 PM, "ddf" <orat...@msn.com> wrote: > > > 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 > -- 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
