On Nov 2, 7:38 pm, Jignesh Makwana <[email protected]> wrote:
> Would you please try with dba_tables?
>
> Regards,
> Jignesh Makwana
> On Nov 3, 2011 4:17 AM, "ddf" <[email protected]> wrote:
>
To add to the prior post if one does have a direct grant on DBA_TABLES
and creates a procedure it depends upon how AUTHID is defined:
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 owner, table_name from dba_tables
where table_name like '%'||upper(p_tabstr) and owner = 'BUNG') loop
7 sqltxt := 'alter table '||rec.owner||'.'||
rec.table_name||' rename column colname to yertz';
8 execute immediate sqltxt;
9 end loop;
10
11 end;
12 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> grant execute on rename_cols to bung;
Grant succeeded.
SQL>
The procedure creates without error due to the direct grant. Allowing
another user absent the direct grant to execute it can be a problem
with the default AUTHID
SQL> connect bung/bing
Connected.
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> exec bing.rename_cols('yuppo')
BEGIN bing.rename_cols('yuppo'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "BING.RENAME_COLS", line 8
ORA-06512: at line 1
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> connect bing/#####
Connected.
SQL>
SQL> create or replace procedure rename_cols (p_tabstr IN varchar2)
2 authid current_user
3 is
4 sqltxt varchar2(2000);
5 begin
6 for rec in (select owner, table_name from dba_tables
where table_name like '%'||upper(p_tabstr) and owner = 'BUNG') loop
7 sqltxt := 'alter table '||rec.owner||'.'||
rec.table_name||' rename column colname to yertz';
8 execute immediate sqltxt;
9 end loop;
10
11 end;
12 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> connect bung/bing
Connected.
SQL>
SQL> drop table yuppo purge;
Table dropped.
SQL> drop table blue_yuppo purge;
Table dropped.
SQL> drop table red_yuppo purge;
Table dropped.
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> exec bing.rename_cols('yuppo')
PL/SQL procedure successfully completed.
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)
YERTZ DATE
SQL> describe red_yuppo
Name Null? Type
----------------------------------------- --------
----------------------------
SMORT NUMBER
ALBEISTER VARCHAR2(20)
YERTZ DATE
SQL>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
