Oracle – Why DBMS_METADATA.GET_DDL Fails with SELECT_CATALOG_ROLE

oracleoracle-10gpermissions

I am trying to get the DDL using METADATA_DBMS.GET_DDL for an object in a database, and I get the following error:

ORA-31603: object "MY_PACKAGE" of type PACKAGE not found in schema "S1"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3149
ORA-06512: at "SYS.DBMS_METADATA", line 4787
ORA-06512: at line 1

This is how I am calling the METADATA_DBMS.GET_DDL:

SELECT DBMS_METADATA.GET_DDL (object_type, object_name, owner)
       FROM dba_objects
       WHERE OBJECT_ID = (select OBJECT_ID from dba_objects where object_name ='MY_PACKAGE' and object_type = 'PACKAGE' and owner = 'S1')

I wrote it with the subquery to make sure the object exists in the schema S1. I am certain S1.MY_PACKAGE exists, I can even look at it in TOAD Schema Browser.

I have the following roles granted:

select * from USER_ROLE_PRIVS 

USERNAME  GRANTED_ROLE         ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
MYUSER    CONNECT              NO              YES          NO
MYUSER    DBA                  NO              YES          NO
MYUSER    SELECT_CATALOG_ROLE  NO              YES          NO

I had DBA Role first, and since it was throwing that error I granted myself SELECT_CATALOG_ROLE.

I checked against another Database Instance where the query works and I have the same roles, what else do I need to make DBMS_METADATA.GET_DDL work?

Both databases are a clone from the same source database, and I later got the privileges granted.

Best Answer

It seems there was some issue with TOAD. I opened TOAD after they granted me DBA Role, yet it was like it did not take effect.

I could query the USER_ROLE_PRIVS table and see I had the role, I could GRANT myself the SELECT_CATALOG_ROLE, but when I called DBMS_METADATA.GET_DDL it did not work.

Since I had open TOAD after the access had been granted, I didn't think that was the issue. At last, as a desperate measure, I tried closing everything and Opening it again and it worked.

I was going to delete this question, but I guess it could be useful for someone else in the future.

Related Question