DBMS_METADATA.GET_DDL Returns Error When Select Types Ora-31603 (Doc ID 312883.1)

Last updated on DECEMBER 06, 2016

Applies to:

PL/SQL - Version 10.1.0.3 and later
Oracle Database - Enterprise Edition - Version 10.1.0.3 and later
Information in this document applies to any platform.
***Checked for relevance on 13-Sep-2016***


Symptoms

Using the DBMS_METADATA.GET_DDL command to access an object from another schema fails with an error:

Error:
---------
ORA-31603: object "<object name>" of type TYPE not found in schema "<schema>"

Sample Code:
***************

Connect as User1
============

Create the object
-------------------

CREATE TYPE OBJECT1 AS OBJECT (FIELD1 NUMBER);
/


Create the Procedure
------------------------

CREATE OR REPLACE PROCEDURE TEST IS
    V_OUTPUT varchar2(1000);
BEGIN
    V_OUTPUT :=  DBMS_METADATA.GET_DDL('TYPE', 'OBJECT1', 'USER1' );
    DBMS_OUTPUT.PUT_LINE ( V_OUTPUT);
END;
/

Test the procedure from the schema owning the analyst
------------------------------------------------------

SQL> SET SERVEROUTPUT ON
SQL> EXECUTE TEST;

  CREATE OR REPLACE TYPE "USER1"."OBJECT1" AS OBJECT (FIELD1 NUMBER);

 

 

Connect as User2
============

 

Create the Procedure
------------------------

CREATE OR REPLACE PROCEDURE TEST IS
    V_OUTPUT varchar2(1000);
BEGIN
    V_OUTPUT :=  DBMS_METADATA.GET_DDL('TYPE', 'OBJECT1', 'USER1' );
    DBMS_OUTPUT.PUT_LINE ( V_OUTPUT);
END;
/

Test the procedure as the other user
------------------------------------

SQL> SET SERVEROUTPUT ON
SQL> EXECUTE TEST;

SQL> EXECUTE TEST;
BEGIN TEST; END;

*
ERROR at line 1:
ORA-31603: object "OBJECT1" of type TYPE not found in schema "USER1"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at "USER2.TEST", line 4
ORA-06512: at line 1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms