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 

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.

1 Answer 1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.