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 stored procedure to grantees. 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 cover all SYS packages that should be granted to PUBLIC, you may follow the link and have a look.
Thank you so much for your support..
My pleasure!