Re: dbms_metadata.get_ddl Error

Understand that, but the issue is what grant(s) am I missing?  That's the 
question Oracle Support can't answer yet either.
  
Thanks!

Robert Freeman <robertgfreeman@xxxxxxxxx> wrote:
  Grant to role vs. direct grant? Anytime something
"works from SQL*Plus" but does not work from PL/SQL
that is one of the first questions to ask.

RF
--- A Ebadi wrote:

> Here is the exact call and the user calling this
> procedure has DBA as well as the procedure owner:
> 
> select
>
dbms_metadata.get_ddl('INDEX','MY_TAB_IDX5','OPS$ORACLE')
> into v_index_info from dual;
> 
> The ironic thing is a similar select works just
> fine from SQLPLUS, but inside the procedure it fails
> with ORA-31603.
> 
> Thanks,
> Abdul
> 
> 
> Ghassan Salem wrote:
> How are you calling dbms_metadata.get_ddl (i.e.
> are you passing the correct schema to it)?
> second, the calling user does have the rights to do
> this on the index's schema?
> 
> rgds
> 
> On 10/15/07, A Ebadi wrote: 
> We have a procedure which calls
> dbms_metadata.get_ddl to get the ddl for an index &
> this works fine for objects (indexes) owned by the
> same user as my procedure. For all other objects I
> get the error below. I have an SR open with Oracle
> for several days now, but no solution has been
> given. Any advise would be appreciated. 
> 
> Environment: Oracle 10.2.0.3.0 on Sun Solaris
> 2.10.
> 
> Here is the error:
> ORA-31603: object "MY_TAB_IDX5" of type INDEX not
> found in schema "OPS$ORACLE" 
> 
> Thanks,
> Abdul
> 
> 
> ---------------------------------
> Be a better Heartthrob. Get better relationship
> answers from someone who knows.
> Yahoo! Answers - Check it out. 
> 
> 
> 
> 
> 
> ---------------------------------
> Be a better Globetrotter. Get better travel answers
> from someone who knows.
> Yahoo! Answers - Check it out.


Robert G. Freeman
Author:
Now Available for Pre-Sales on Amazon.com!!!!
**************************************************
Oracle Database 11g New Features (Oracle Press)
**************************************************
Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Oracle9i RMAN Backup and Recovery (Oracle Press)
Oracle9i New Feature
Blog: http://robertgfreeman.blogspot.com (Oracle Press)


       
---------------------------------
Need a vacation? Get great deals to amazing places on Yahoo! Travel.

Other related posts: