Home » SQL & PL/SQL » SQL & PL/SQL » retrieve store procedure DDL in other schema
icon8.gif  retrieve store procedure DDL in other schema [message #192792] Wed, 13 September 2006 11:06 Go to next message
Messages: 1
Registered: September 2006
Junior Member
Currently I have a sysdba account and I would like to retrieve other user's procedure. Just read the oracle document.
"In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled. Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights."

But not sure how to do it. Any one can give much more detailed steps?

Thanks in advance....
My previous steps:

1) log in sql+ as sysdba
2) select DBMS_METADATA.GET_DDL(object_type, object_name) from all_objects where object_type='PROCEDURE' and owner='<other owner name>'

Then get error:
ORA-31603: object "SP_INS_RE_MPC" of type PROCEDURE not found in schema "SYS"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

[Updated on: Wed, 13 September 2006 11:09]

Report message to a moderator

Re: retrieve store procedure DDL in other schema [message #192793 is a reply to message #192792] Wed, 13 September 2006 11:30 Go to previous message
Messages: 25581
Registered: January 2009
Location: SoCal
Senior Member
select DBMS_METADATA.GET_DDL(object_type, object_name,schema_owner) from dual;
Previous Topic: Need to generate hierarchical recursive XML using SQL
Next Topic: Automating adding partitions by using data dictionary
Goto Forum:

Current Time: Thu Aug 24 03:57:28 CDT 2017

Total time taken to generate the page: 0.04607 seconds