Re: dbms_metadata.get_ddl Error
- From: Maxim Demenko <mdemenko@xxxxxxxxx>
- To: ebadi01@xxxxxxxxx
- Date: Mon, 15 Oct 2007 18:56:33 +0200
A Ebadi schrieb:
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
I believe, it is mentioned somewhere in metalink,
however, you could read this reference:
http://sql.ru/forum/actualthread.aspx?tid=354978&hl=ora+31603 ( it is in
russian, but you can try to translate it via google or similar web service),
shortly, the issue is caused by this snippet in all sys.ku$_%_view for
relevant objects :
AND (SYS_CONTEXT('USERENV','CURRENT_USERID') IN (o.owner_num, 0) OR
EXISTS ( SELECT * FROM session_roles
WHERE role='SELECT_CATALOG_ROLE' ))
So,you have basically following choices
1) execute it as owner
2) execute it as sysdba
3) have SELECT_CATALOG_ROLE and procedure with authid current_user
Briefly it mentioned also here
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#sthref4208
Best regards
Maxim
--
//www.freelists.org/webpage/oracle-l
Other related posts:
