ORA-31603 when using DBMS_METADATA 1

[Thargy]

I am using 11g Release 11.2.0.1.0 - 64bit Production
and am logged in as SYS as SYSDBA.

I am attempting to extract the DDL for tables in several schemas. Each schema name begins with 'JOB_' and I am using this as a filtering condition.

The following sql

SELECT DBMS_METADATA.GET_DDL('TABLE',table_name) DDL_STRING,OWNER,table_name
FROM ALL_TABLES
         WHERE SUBSTR(OWNER,1,4) = 'JOB_'
           AND OWNER != 'SYS'
           AND SUBSTR(table_name,1,3) != ('MV_') --Ignore materialized views
           AND table_name NOT LIKE ('%$%') --Ignore materialized view logs
           AND table_name NOT IN (SELECT MVIEW_NAME
                                    FROM ALL_MVIEWS
                                   WHERE SUBSTR(OWNER,1,4)= 'JOB_')
      ORDER BY OWNER,table_name;

yields the following error

ORA-31603: object "CURRICULUM_DETAIL" of type TABLE not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 2625
ORA-06512: at "SYS.DBMS_METADATA", line 2668
ORA-06512: at "SYS.DBMS_METADATA", line 2983
ORA-06512: at "SYS.DBMS_METADATA", line 3897

I have googled and found that this is a known bug. A search of the knowledge base on MOS reveals the "solution" to be

Cause
The appropriate privileges have not been granted to the schema executing the procedure as the same procedure works fine from the schema that owns the object and from SYS.
Solution

Step 1: Execute "GRANT SELECT_CATALOG_ROLE TO <schema>;" as SYS or another user with the privilege.
Step 2. Modify the procedure to include "AUTHID CURRENT_USER"

Since I am logged in as SYS, I am surprised to have any problems, as the oracle note above explicitly says that the procedure works fine from SYS.

I have also granted SELECT_CATALOG_ROLE to the user experiencing the problem, but this has had no effect.

Can anyone assist?

Regards

T

 

[Dagon]

You are not putting a schema name in the call to get_ddl, so it will assume that the table is in the SYS schema.

 FUNCTION get_ddl (
                object_type     IN  VARCHAR2,
                name            IN  VARCHAR2,
                [b]schema          IN  VARCHAR2 DEFAULT NULL[/b],
                version         IN  VARCHAR2 DEFAULT 'COMPATIBLE',
                model           IN  VARCHAR2 DEFAULT 'ORACLE',
                transform       IN  VARCHAR2 DEFAULT 'DDL')
        RETURN CLOB;

-- GET_DEPENDENT_XML:   Return the metadata for objects dependent on a
--      base object as XML.
--      This interface is meant for casual browsing (e.g., from SQLPlus)
--      vs. the programmatic OPEN / FETCH / CLOSE interfaces above.
-- PARAMETERS:
--      object_type     - The type of object to be retrieved.
--      base_object_name- Name of the base object.
--      base_object_schema- Schema containing the base object.  [b]Defaults to
--                        the caller's schema.[/b]

 

[Thargy]

Thanks Dagon,

I somehow felt I was missing something obvious - and I was.

I'll give it a whirl and report back shortly.

Regards

T

 

[Thargy]

Dagon,

the following

SELECT DBMS_METADATA.GET_DDL('TABLE',table_name,schema => owner) DDL_STRING,OWNER,table_name
FROM ALL_TABLES
         WHERE SUBSTR(OWNER,1,4) = 'JOB_'
           AND OWNER != 'SYS'
           AND SUBSTR(table_name,1,3) != ('MV_') --Ignore materialized views
           AND table_name NOT LIKE ('%$%') --Ignore materialized view logs
           AND table_name NOT IN (SELECT MVIEW_NAME
                                    FROM ALL_MVIEWS
                                   WHERE SUBSTR(OWNER,1,4)= 'JOB_')
      ORDER BY OWNER,table_name;

is now bringing back results.

<doffs cap>

Regards

T