Wednesday, December 16, 2015

How to resolve PLS-00201: identifier 'DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION'

During migration of an EBS (E-Business Suite) database, I was left with some packages in the APPS schema that couldn't be compiled.

SQL> alter package apps.ad_parallel_updates_pkg compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY APPS.AD_PARALLEL_UPDATES_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
338/8    PL/SQL: Statement ignored
338/8    PLS-00201: identifier 'DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION'
         must be declared

I first tried granting execute on dbms_system directly to the user app - no luck.

Then I found the following blog post

https://blogs.oracle.com/mandalika/entry/resolving_pls_00201_identifier_dbms

Which points to the following solution:

SQL> create public synonym dbms_system for dbms_system;

Synonym created.

SQL> grant execute on dbms_system to apps;

Grant succeeded.

SQL> alter package apps.ad_parallel_updates_pkg compile body;

Package body altered.

SQL>

No comments:

Post a Comment