Calling Dbms_metadata.Get_ddl From Stored Procedure Results Ora-31603_463483.1

rongshiyuan發表於2015-03-12

Calling Dbms_metadata.Get_ddl From Stored Procedure Results In Ora-31603 (文件 ID 463483.1)


In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.3
Information in this document applies to any platform.

Symptoms

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:
ORA-31603: object of type not found in schema
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
ORA-06512: at , line 16
ORA-06512: at line 1

Calling the dbms_metadata.get_ddl directly from sql*plus on the same object works fine.

Cause

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.

Solution

1- Create the procedure with AUTHID CURRENT_USER
2- Grant SELECT ANY DICTIONARY privilege to any user calling the procedure

OR

1- Create the procedure in the SYS schema
2- Grant execute on the procedure to the chosen users.

References

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-1457028/，如需轉載，請註明出處，否則將追究法律責任。

相關文章