Re: dbms_metadata.get_ddl Error

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

--
http://www.freelists.org/webpage/oracle-l