On Nov 2, 7:38 pm, Jignesh Makwana <makwanajigne...@gmail.com> wrote: > Would you please try with dba_tables? > > Regards, > Jignesh Makwana > On Nov 3, 2011 4:17 AM, "ddf" <orat...@msn.com> wrote: > > > > > > > On Nov 1, 7:45 pm, Jignesh Makwana <makwanajigne...@gmail.com> wrote: > > > You write nonsense and you also agree with the nonsense to some level. > > > You can prove that statement? > > > > 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: > > > Where are the explicit privileges on USER_TABLES in this series of > > grants: > > > SQL> grant connect, create table, plustrace to bong; > > > Grant succeeded. > > > SQL> grant alter any procedure to bong; > > > Grant succeeded. > > > SQL> grant create any procedure to bong; > > > Grant succeeded. > > > SQL> grant create procedure to bong; > > > Grant succeeded. > > > SQL> grant debug any procedure to bong; > > > Grant succeeded. > > > SQL> grant drop any procedure to bong; > > > Grant succeeded. > > > SQL> grant execute any procedure to bong; > > > Grant succeeded. > > > SQL> grant create synonym to bong; > > > Grant succeeded. > > > SQL> grant create view to bong; > > > Grant succeeded. > > > SQL> grant create any type to bong; > > > Grant succeeded. > > > SQL> grant create any sequence to bong; > > > Grant succeeded. > > > SQL> grant drop any sequence to bong; > > > Grant succeeded. > > > SQL> > > > The example still runs without error, and no explicit privileges on > > USER_TABLES have been granted: > > > SQL> > > SQL> connect bong/###### > > Connected. > > SQL> > > SQL> set echo on > > SQL> > > SQL> create table yappo( > > 2 smort number, > > 3 albeister varchar2(20), > > 4 colname date > > 5 ); > > > Table created. > > > SQL> > > SQL> create table blue_yappo( > > 2 smort number, > > 3 albeister varchar2(20), > > 4 colname date > > 5 ); > > > Table created. > > > SQL> > > SQL> create table red_yappo( > > 2 smort number, > > 3 albeister varchar2(20), > > 4 colname date > > 5 ); > > > Table created. > > > SQL> > > 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> > > SQL> create table yuppo( > > 2 smort number, > > 3 albeister varchar2(20), > > 4 colname date > > 5 ); > > > Table created. > > > SQL> > > SQL> create table blue_yuppo( > > 2 smort number, > > 3 albeister varchar2(20), > > 4 colname date > > 5 ); > > > Table created. > > > SQL> > > SQL> create table red_yuppo( > > 2 smort number, > > 3 albeister varchar2(20), > > 4 colname date > > 5 ); > > > Table created. > > > SQL> > > 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- Hide quoted text - > > - Show quoted text -
Certainly: SQL> create table yappo( 2 smort number, 3 albeister varchar2(20), 4 colname date 5 ); Table created. SQL> SQL> create table blue_yappo( 2 smort number, 3 albeister varchar2(20), 4 colname date 5 ); Table created. SQL> SQL> create table red_yappo( 2 smort number, 3 albeister varchar2(20), 4 colname date 5 ); Table created. SQL> 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 dba_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> SQL> create table yuppo( 2 smort number, 3 albeister varchar2(20), 4 colname date 5 ); Table created. SQL> SQL> create table blue_yuppo( 2 smort number, 3 albeister varchar2(20), 4 colname date 5 ); Table created. SQL> SQL> create table red_yuppo( 2 smort number, 3 albeister varchar2(20), 4 colname date 5 ); Table created. SQL> 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 dba_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> In an anonymous PL/SQL block no direct grant on a dynamic view is necessary; access by role will work just fine. Should one want to create a procedure THEN a direct grant is required: SQL> create or replace procedure rename_cols (p_tabstr IN varchar2) 2 authid definer 3 is 4 sqltxt varchar2(2000); 5 begin 6 for rec in (select table_name from dba_tables where table_name like '%'||upper(p_tabstr)) loop 7 sqltxt := 'alter table '||rec.table_name||' rename column colname to yertz'; 8 execute immediate sqltxt; 9 end loop; 10 11 end; 12 / Warning: Procedure created with compilation errors. SQL> SQL> show errors Errors for PROCEDURE RENAME_COLS: LINE/COL ERROR -------- ----------------------------------------------------------------- 6/13 PL/SQL: SQL Statement ignored 6/36 PL/SQL: ORA-00942: table or view does not exist 7/3 PL/SQL: Statement ignored 7/29 PLS-00364: loop index variable 'REC' use is invalid SQL> Your original statement states: "1. You may not be able to use dynamic view in pl sql. Use your own table containing the required table names. " That is not true as anonymous blocks have no issue with access through roles. 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
