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.

Labels: oracle pls-00201 dbms_system

Posted by: Giri Mandalika.    # 6:47 PM 0 comments.     Tweet