ITPub博客

首页 > 数据库 > Oracle > Calling Dbms_metadata.Get_ddl From Stored Procedure Results Ora-31603_463483.1

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

Oracle 作者:rongshiyuan 时间:2015-03-12 11:33:05 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-11-24

  • 博文量
    798
  • 访问量
    3206442