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: 24964
Registered: January 2009
Location: SoCal
Senior Member
select DBMS_METADATA.GET_DDL(object_type, object_name,schema_owner) from dual;
Previous Topic: Logical Delete
Next Topic: ORA-00911 error....
Goto Forum:

Current Time: Thu Oct 27 06:16:03 CDT 2016

Total time taken to generate the page: 0.12368 seconds