Re: dbms_metadata.get_ddl Error

From: A Ebadi <ebadi01_at_yahoo.com>
Date: Mon, 15 Oct 2007 08:31:10 -0700 (PDT)
Message-ID: <578373.11971.qm@web51107.mail.re2.yahoo.com>

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.

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

Received on Mon Oct 15 2007 - 10:31:10 CDT