ORA-28110: Policy function or package has error

I got ” ORA-28110: Policy function or package has error ”  error in Oracle database.

ORA-28110: Policy function or package has error

Details of error are as follows.

On : 12.1.3 version, Transaction Entry

An error is occurring when attempting to process an RMA Outbound using third party software call as seen below:

CALL APPS.wwbts_returns_outbound_pkg.wwont_returns_outbound(:errBuff, :retCode, :p_ont_return, :invNumber)} and ‘500’ value for invNumber parameter

ERROR
-----------------------
ORA-20001: An error was encountered - 1 -ERROR- - exception occured - ORA-20001: SQL_PLSQL_ERROR: N, ROUTINE, HR_SIGNON.INITIALIZE_HR_SECURITY, N, ERRNO, -28110, N, REASON, ORA-28110: policy function or package APPS.FND_PROFILE_OPTION_VALUES= has error


STEPS
-----------------------
The issue is occurring when invoking a third party package.

Policy function or package has error

This ORA-28110 error is related to the policy function has compilation errors or does not exist, DBMS_RLS.ADD_POLICY will successfully complete but SELECT or DML will result in ORA-28110: policy function or package <schema.name> has error.

Please recompile the (packaged) function with the following statement:

CREATE OR REPLACE FUNCTION | PACKAGE or ALTER FUNCTION | PACKAGE COMPILE [BODY] 

On : 12.2 version, Autoconfig Engine

After completing Export/Import to migrate Database run the following can cause ORA-28110:

adautoconfig

adcmctl.sh

fnd_conc_clone.setup_clean



ERROR
-----------------------

- adautoconfig see :


afdbprf.sh started at Tue Nov 25 10:49:29 CST 2014


The environment settings are as follows ...

  ORACLE_HOME : /<DIR>/product/11.2.0/dbhome_1
  ORACLE_SID : <SID>
  PATH : /<DIR>/product/11.2.0/dbhome_1/perl/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/orawms/bin:/<DIR>/product/11.2.0/dbhome_1/bin:.
  Library Path : /<DIR>/product/11.2.0/dbhome_1/lib:/usr/X11R6/lib:/usr/openwin/lib:/<DIR>/product/11.2.0/dbhome_1/lib:/usr/dt/lib:/<DIR>/product/11.2.0/dbhome_1/ctx/lib

Executable : /<DIR>/product/11.2.0/dbhome_1/bin/sqlplus


SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 25 10:49:29 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
begin
*
ERROR at line 1:
ORA-28110: policy function or package APPS.FND_PROFILE_OPTION_VALUES has error
ORA-06512: at "APPS.ADX_PRF_PKG", line 52
ORA-06512: at line 7

-- exec fnd_conc_clone.setup_clean;    see

ERROR at line 1:
ORA-28110: policy function or package APPS.FND_CONCURRENT_QUEUE_SIZE has error
ORA-06512: at "APPS.FND_CONC_CLONE", line 354
ORA-06512: at line 1

STEPS
———————–
The issue can be reproduced at will with the following steps:

1. Run adautoconfig

or

2. from sqlplus >exec fnd_conc_clone.setup_clean;

or

3. run script adcmctl.sh status

DB Bug when doing export/import

This is Bug 14005749 : IMPDP FAILS TO IMPORT FUNCTION NAMES (HAVING = CHAR) ASSOCIATED WITH VPD POLICY
Description : IMPDP may not import the function names associated with VPD Policy correctly when they are created with special characters (like an “=” character) in their name.

This can be confirmed with the following SQL :

SQL> SELECT OBJECT_OWNER, FUNCTION,policy_name, OBJECT_NAME FROM DBA_POLICIES where OBJECT_NAME like 'FND_PROFILE_OPTION%';

OBJECT_OWNER FUNCTION
------------------------------ ------------------------------
POLICY_NAME OBJECT_NAME
------------------------------ ------------------------------
APPLSYS FND_PROFILE_OPTIONS
ZD_SEED FND_PROFILE_OPTIONS#

APPLSYS FND_PROFILE_OPTIONS_TL
ZD_SEED FND_PROFILE_OPTIONS_TL#


expect to see FUNCTION as FND_PROFILE_OPTIONS=

During the import process many FUNCTIONs would have been altered as the import process removed the = that was part of the FUNCTION name.  As the FUNCTION name has changed it is resulting in the reported errors.  This bugs existed in pre 11.2.0.4.  A patch is available for 11.2.0.3.  Based on the bug details problem is during the Import so should be able to used the same export data and just redo the import.

There are over 2500 FUnctions with “=”  in the name so its possible to see this issue in many other places than whats been reported in this SR.

It will be necessary to apply a DB patch on the target system and redo the import.

1. Download and Apply DB Patch 14005749 on the Target system
2. Recreate an empty DB and redo any necessary steps on the Target as per (Doc ID 1613716.1)
3. Import again.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )