| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Error ORA-31603 while extracting DDL using dbms_metadata
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
Best Regards,
Prasad
 |
 |