Re: dbms_metadata.get_ddl Error

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

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