Re: dbms_metadata.get_ddl Error

From: Robert Freeman <robertgfreeman_at_yahoo.com>
Date: Mon, 15 Oct 2007 08:58:56 -0700 (PDT)
Message-ID: <258866.26921.qm@web38915.mail.mud.yahoo.com>

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 <ebadi01_at_yahoo.com> 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 <salem.ghassan_at_gmail.com> 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 <ebadi01_at_yahoo.com> 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)

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

Received on Mon Oct 15 2007 - 10:58:56 CDT