I have an issue using dbms_metadata.get_ddl, specifically the problem exits when get_ddl is called from inside a package and needs to extract the ddl from a table in another schema.
The normal and documented behavior of dbms_metadata.get_ddl is:
- When called interactively (outside of a package or a procedure), get_ddl can operate on a different schema only if the user has the select_catalog_role (this can be checked with “SELECT role FROM session_roles WHERE role='SELECT_CATALOG_ROLE'”)
- When called from inside a package, the package must be created as AUTHID CURRENT_USER, because roles are not valid inside packages. FYI, this is the piece of code inside DBMS_metadata that check for permissions: ... AND (SYS_CONTEXT('USERENV','CURRENT_USERID') IN(o.owner_num,0) OR EXISTS(SELECT * FROM session_roles WHERE role='SELECT_CATALOG_ROLE'))
Under either of the above circumstances there are no issues, get_ddl works fine. The problem is that, in my case, due to the structure of the application, the main operations flow must be contained in a package with AUTHID DEFINER. I tried to put the call to get_ddl into a separate package with AUTHID CURRENT_USER, but it doesn’t work, because the calling package has AUTHID DEFINER.
The following sequence:
Main package (AUTHID DEFINER) ==> Utilities package (AUTHID CURRENT_USER) ==> dbms_metadata.get_ddl('xxx', 'yyy')
Generates the error ORA-31603: object "xxx" of type TABLE not found in schema "yyy"
I hope there is a solution that I just can’t see, any suggestions are welcome