Skip to content

How to Resolve PLS-00201: identifier 'DBMS_SQL' must be declared

  • by

PLS-00201

We have talked about some common patterns of PLS-00201. Here is another case.

Found some packages of CTXSYS are invalid, most compile errors are in the same pattern, PLS-00201.

PLS-00201: identifier 'DBMS_SQL' must be declared PLS-00201: identifier 'DBMS_SCHEDULER' must be declared PLS-00201: identifier 'DBMS_LOB' must be declared PLS-00201: identifier 'DBMS_JOB' must be declared PLS-00201: identifier 'UTL_HTTP' must be declared

Most likely, you don't have EXECUTE privilege on the object to handle.

In this case, those packages owned by SYS are open to PUBLIC and very commonly used by users.

Solution

For normal cases, we should grant the proper privilege, usually EXECUTE on the object to the owner of stored procedure. In this case, we know we should grant EXECUTE on those packages to PUBLIC again.

SQL> conn / as sysdba
Connected.
SQL> GRANT EXECUTE ON SYS.DBMS_SQL TO PUBLIC;

Grant succeeded.

SQL> GRANT EXECUTE ON SYS.DBMS_SCHEDULER TO PUBLIC;

Grant succeeded.

SQL> GRANT EXECUTE ON SYS.DBMS_LOB TO PUBLIC;

Grant succeeded.

SQL> GRANT EXECUTE ON SYS.DBMS_JOB TO PUBLIC;

Grant succeeded.

SQL> GRANT EXECUTE ON SYS.UTL_HTTP TO PUBLIC;

Grant succeeded.

To know what SYS packages are granted to PUBLIC, you may use the following statement.

SQL> select table_name from dba_tab_privs where owner = 'SYS' and grantee = 'PUBLIC' and privilege = 'EXECUTE' and TYPE = 'PACKAGE' order by 1;

Leave a Reply

Your email address will not be published. Required fields are marked *