Resolving "PLS-00201: identifier 'DBMS_SYSTEM.XXXX' must be declared" Error
- by Giri Mandalika
Here is a failure sample.
SQL set serveroutput on
SQL alter package APPS.FND_TRACE compile body;
Warning: Package Body altered with compilation errors.
SQL show errors
Errors for PACKAGE BODY APPS.FND_TRACE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
235/6 PL/SQL: Statement ignored
235/6 PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared
..
By default, DBMS_SYSTEM package is accessible only from SYS schema. Also there is no public synonym created for this package. So, the solution is to create the public synonym and grant "execute" privilege on DBMS_SYSTEM package to all database users or a specific user.
eg.,
SQL CREATE PUBLIC SYNONYM dbms_system FOR dbms_system;
Synonym created.
SQL GRANT EXECUTE ON dbms_system TO APPS;
Grant succeeded.
- OR -
SQL GRANT EXECUTE ON dbms_system TO PUBLIC;
Grant succeeded.
SQL alter package APPS.FND_TRACE compile body;
Package body altered.
Note that merely granting execute privilege is not enough -- creating the public synonym is as important to resolve this issue.
