Oracle Server - Enterprise Edition - Version: 184.108.40.206 to 10.2.0.3
Information in this document applies to any platform.
Calling dbms_metadata.get_ddl from a stored procedure to get the DDL of an object in another schema raises the following error:
ERROR at line 1:
Calling the dbms_metadata.get_ddl directly from sql*plus on the same object works fine.
This problem was diagnosed in Bug 3960099 - ORA-31603 WHEN CALLING DBMS_METADATA FROM PROCEDURE
Calling dbms_metadata.get_ddl on objects not owned by the caller requires being granted the "select_catalog_role" role. When granted that role, the caller can get DDL of any object when calling dbms_metadata.get_ddl from Sql*Plus. In a stored procedure roles are disabled so the caller can get the DDL of it's own objects only.
1- Create the procedure with AUTHID CURRENT_USER
2- Grant SELECT ANY DICTIONARY privilege to any user calling the procedure
1- Create the procedure in the SYS schema
2- Grant execute on the procedure to the chosen users.
BUG:2756450 - DBMS_METADATA.GET_DDL RAISES ORA-31603 WHEN CALLED FROM FUNCTION BUG:3047487 - DBMS_METADATA.GET_DDL RAISES ORA-30603 WHEN USED WITH OBJECT IN ANOTHER SCHEMA BUG:3960099 - ORA-31603 WHEN CALLING DBMS_METADATA FROM PROCEDURE BUG:6080955 - ORA-31603: ERROR WHEN USING DBMS_METADATA.GET_DDL