Using PL/SQL and SQL to Change Profile Options for Oracle R12 EBS

By Ben Prusinski

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.