Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Error ORA-31603 while extracting DDL using dbms_metadata

Error ORA-31603 while extracting DDL using dbms_metadata

From: <>
Date: Wed, 23 Jun 2004 12:41:36 -0400
Message-ID: <>

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 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,

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:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Wed Jun 23 2004 - 11:45:45 CDT

Original text of this message