| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: dbms_metadata.get_ddl Error
Even running the procedure as SYSDBA or granting user SELECT_CATALOG_ROLE won't work - same error.
Jared Still <jkstill_at_gmail.com> wrote:
On 10/15/07, A Ebadi <ebadi01_at_yahoo.com> wrote: Tried to give select_catalog_role with authid current_user, but doesn't work as need lots of privs for the executing user like drop any index, select any table, etc.
If using the DBA role didn't work, granting SELECT_CATALOG_ROLE isn't going to help.
What we are looking for is a simple procedure that allows users to drop an index, but saves the index re-create ddl before dropping it.
Just tested you exact scenario.
ORA-31603 will occur unless the user running the procedure has explicitly granted SELECT on the table in question.
Not sure why Oracle support has not gotten back to you. It takes < 5 minutes to setup a user and test this.
Any other suggestions?
Yes, use Perl.
Specifically, Perl with the Oracle::DDL module.
Or just use dbms_metadata from a SQL script, and generate and run the SQL.
Or run the procedure as sysdba.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
 |
 |