Error fetching scripts in Oracle

WinSQL uses DBMS_METADATA package available in Oracle to fetch scripts for views, stored procedures and triggers. Often users receive the following error message when fetching this script:

Error: ORA-31603: object "YOUR_OBJECT" of type TRIGGER not found
in schema "YOUR_SCHEMA"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2806
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

The most common reason for such an error is a permission issue. Following text is copied from Oracle documentation regarding security issues with DBMS_METADATA.

Security Model

The object views of the Oracle metadata model implement security as follows:
  • Non-privileged users can see the metadata of only their own objects.
  • SYS and users with SELECT_CATALOG_ROLE can see all objects.
  • Non-privileged users can also retrieve public synonyms, system privileges granted to them, and object privileges granted to them or by them to others. This also includes privileges granted to PUBLIC.
  • If callers request objects they are not privileged to retrieve, no exception is raised; the object is simply not retrieved.
  • If non-privileged users are granted some form of access to an object in someone else's schema, they will be able to retrieve the grant specification through the Metadata API, but not the object's actual metadata.
In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled. Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights.

Click here to learn more about DBMS_METADATA package.


Social Media

Powered by