Using PL/SQL and SQL to change profile options for Oracle R12 EBS

Recently I had to fix an unusual problem with Oracle R12 (12.1.1) E-Business Suite system after changing some default timeout parameters. Since I was unable to login to the Oracle Applications Manager (OAM) graphical interface and had weird errors below, I needed a way to switch these profile values back to the lower default values. I set the ICX timeout parameters (ICX: Session Timeout, ICX: Limit Time, and ICX: Limit Connect) way too high in our test environment to experiment with user logout issues. SO I had the below errors:

Oracle error 1841: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1468 ORA-06512: at line 1 has been detected in FND_SESSION_MANAGEMENT.CHECK_SESSION.

Oracle error 1841: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1468 ORA-06512: at "

APPS.FND_SESSION_MANAGEMENT", line 1236 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 421 ORA-06512: at line 1 has been detected in FND_AOLJ_UTIL.is_Valid_ICX.

Servlet error: An exception occurred. The current application deployment descriptors do not allow for including it in this response. Please consult the application log for details.


Needless to say this was extremely frustrating problem to solve as the few hits on My Oracle Support (formerly Metalink) along with an SR to support were unable to provide me with the solution and root cause. Finally after some research and discussion with a very smart support manager from Oracle (thank you Warwick!), I used a SQL and PL/SQL script to reset the values to much lower timeout settings and then I cleared the web browser cache. This solved the issue.

Sometimes you may not want to login or be able to access the OAM browser due to such issues. In this case, you want to use a SQL and/or PL/SQL wrapper script to modify profile settings. Below is the script to check for current values of profiles within Oracle R12 EBS:


set serveroutput on
set echo on
set timing on
set feedback on
set long 10000
set linesize 120
set pagesize 132
column SHORT_NAME format A30
column NAME format A40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format A60 wrap
spool profile_options.txt
select p.profile_option_name SHORT_NAME, n.user_profile_option_name NAME,
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%&profile_name%')
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, level_set;


The above script queries the Oracle R12 EBS database for values against the FND_PROFILE tables in the APPS schema.
Ok so now that we have our settings for profiles, let's show how we changed these for ICX profile settings to provide the solution.


DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('ICX_LIMIT_CONNECT', '2000', 'SITE');
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
commit;
END;
/


To use the above PL/SQL script to change profile parameters, it uses the FND_PROFILE package for Oracle R12 EBS with the SAVE function within PL/SQL which in turn takes three parameters as shown by the package definition listed below:


SQL> desc FND_PROFILE

FUNCTION BUMPCACHEVERSION_RF RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SUBSCRIPTION_GUID RAW IN
P_EVENT WF_EVENT_T IN/OUT

FUNCTION DEFINED RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN

FUNCTION DELETE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_LEVEL_NAME VARCHAR2 IN
X_LEVEL_VALUE VARCHAR2 IN DEFAULT
X_LEVEL_VALUE_APP_ID VARCHAR2 IN DEFAULT
X_LEVEL_VALUE2 VARCHAR2 IN DEFAULT

PROCEDURE GET
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
VAL VARCHAR2 OUT

FUNCTION GET_ALL_TABLE_VALUES RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DELIM VARCHAR2 IN

PROCEDURE GET_SPECIFIC
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME_Z VARCHAR2 IN
USER_ID_Z NUMBER IN DEFAULT
RESPONSIBILITY_ID_Z NUMBER IN DEFAULT
APPLICATION_ID_Z NUMBER IN DEFAULT
VAL_Z VARCHAR2 OUT
DEFINED_Z BOOLEAN OUT
ORG_ID_Z NUMBER IN DEFAULT
SERVER_ID_Z NUMBER IN DEFAULT

FUNCTION GET_TABLE_VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN

PROCEDURE INITIALIZE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
USER_ID_Z NUMBER IN DEFAULT
RESPONSIBILITY_ID_Z NUMBER IN DEFAULT
APPLICATION_ID_Z NUMBER IN DEFAULT
SITE_ID_Z NUMBER IN DEFAULT
PROCEDURE INITIALIZE_ORG_CONTEXT

PROCEDURE PUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
VAL VARCHAR2 IN

PROCEDURE PUTMULTIPLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAMES VARCHAR2 IN
VALS VARCHAR2 IN
NUM NUMBER IN

FUNCTION SAVE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_VALUE VARCHAR2 IN
X_LEVEL_NAME VARCHAR2 IN
X_LEVEL_VALUE VARCHAR2 IN DEFAULT
X_LEVEL_VALUE_APP_ID VARCHAR2 IN DEFAULT
X_LEVEL_VALUE2 VARCHAR2 IN DEFAULT


FUNCTION SAVE_USER RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_VALUE VARCHAR2 IN

FUNCTION VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN

FUNCTION VALUE_SPECIFIC RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
USER_ID NUMBER IN DEFAULT
RESPONSIBILITY_ID NUMBER IN DEFAULT
APPLICATION_ID NUMBER IN DEFAULT
ORG_ID NUMBER IN DEFAULT
SERVER_ID NUMBER IN DEFAULT

FUNCTION VALUE_WNPS RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN

In our above example, we gave FND_PROFILE.SAVE the required three parameters to set the profile values for ICX:Limit Connect based on time duration of 2000 which allows for up to a maximum of 2000 connections permitted in a single session. We want a large value so that the many batch processes and concurrent jobs processed by functional users can take place without terminating abnormally while processing such things as invoices and order shipments for Oracle Financials within the Oracle R12 E-Business Suite.