Error ORA-31603 while extracting DDL using dbms_metadata

From: Prasada.Gunda@xxxxxxxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Date: Wed, 23 Jun 2004 12:41:36 -0400




When I use dbms_metadata package to extract the index ddl of other
schema(DISCLM) table using the stored procedure
owned by another schema (UTIL), I am getting the following error.  When I
run the same code under anonymous block, it works fine.

ORA-31603: object "IDX_BM_DD_ADTKY" of type INDEX not found in schema
"DISCLM"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

DB is 9.2.0.5 and it is on hp-ux 11. I am aware of that the roles are
disabled during the execution of pl/sql stored procedure unless the
procedure is created under Invoker rights. This procedure created under
Definer rights.

I am not sure which system/object privilege I need to grant for this
procedure to work. I tried using dbms_metadata.set_filter
with SCHEMA_EXPR, BASE_OBJECT_SCHEMA, BASE_OBJECT_OWNER but no success.

Any thoughts or inputs are much appreciated. Thanks for your help.

Code snippet in the stored procedure.

  insert into index_control (owner, table_name, index_name, error_level,
index_script)
  select owner_select_var, table_select_var, q_rec.index_name, 3,
         dbms_metadata.get_ddl('INDEX', q_rec.index_name,
owner_select_var)
  from   dual;


UTIL schema has the following privileges:

Sys Privs :

drop user
analyze any
create user
alter system
alter session
drop any index
drop any table
grant any role
alter any index
create any index
select any table
select any dictionary
unlimited tablespace

Tab privs :

v_$session select
dba_indexes select
dba_ind_columns select
dba_objects select
dba_tables select
dba_tab_columns select
dba_tab_partitions select
dba_ind_partitions select
dbms_lock execute
dbms_metadata execute

Best Regards,
Prasad




*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for 
the exclusive use of addressee and may contain proprietary, confidential and/or 
privileged information.  If you are not the intended recipient, any use, 
copying, disclosure, dissemination or distribution is strictly prohibited.  If 
you are not the intended recipient, please notify the sender immediately by 
return e-mail, delete this communication and destroy all copies.
*************************************************************************

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Error ORA-31603 while extracting DDL using dbms_metadata

Other related posts:
