Using profile option ‘Initialization SQL Statement -Custom’

How to set ‘Initialization SQL Statement – Custom’ (FND_INIT_SQL) profile option?

This is used to get trace files for the concurrent Programs you run through oracle applications. User mainly by technical persons who are trying to solve an issue where manipulating some database session level parameter can help to gather more information.

Using the profile option ‘Initialization SQL Statement – Custom’, you can add site-specific initialization code, such as optimizer settings. This profile value must be a valid SQL statement, or a PL/SQL block for more than one statement, that is to be executed once at the startup of every database session.

The code is executed by FND_GLOBAL.INITIALIZE and APPS_INITIALIZE immediately after initializing global variables, profiles, and the contents of client_info on session startup.

The order of execution is:

FND_GLOBAL values initialized Profiles initialized

CLIENT_INFO contents initialized

FND_APPS_INIT_SQL initialization code called (if a value is defined)

FND_INIT_SQL initialization code called (if a value is defined)

This means that in most cases (eg. tracing a form) the profile option setting will be effective after signing-on again. You can set this profile option at different levels like user/responsibility/ /application or site.

You can specify any valid SQL statement or a PL/SQL block for this profile value, but the best way is using the fnd_sess_ctl procedure of fnd_ctl package.

The value of this profile option can be set using fnd_sess_ctl as follows:
BEGIN
FND_CTL.FND_SESS_CTL(
,
,
,
,
,
);
END;

The different parameters are (all within single quotes) :

-> This sets the Optimizer_mode for online users. The valid values are RULE/CHOOSE/FIRST_ROWS/ALL_ROWS.
It is recommended for CBO you set it to FIRST_ROWS.

-> This sets the Optimizer_mode for conc. jobs. The valid values are RULE/CHOOSE/FIRST_ROWS/ALL_ROWS .

It is recommeneded for CBO you set it to ALL_ROWS.

-> This sets the trace option to true or false. Valid values are TRUE/FALSE. If set to true then you will be able to generate the trace files for the session.

-> This sets the times_statistics for tracing to true or false. Valid values are TRUE/FALSE.

-> Reserved for logging. Logs session information in FND_TRACE_LOG. Recommended value is ”, other possible value is LOG.

-> This can be set for different events. An example could be to set an event point in order to get the values of the bind values in the trace file. In that case the example setting is as follows.

‘ALTER SESSION SET EVENTS = ””””” 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4 ”””’ .

Please make sure you have all the quotes correct.

Examples:
———
1) To enable SQL trace for the session, you should set the profile option as follows:

BEGIN FND_CTL.FND_SESS_CTL(”,”,’TRUE’,'TRUE’,”,”); END;

Result of the above setting will be the execution of the following two alter session commands at database session startup:

ALTER SESSION SET SQL_TRACE = ‘TRUE’
ALTER SESSION SET TIMED_STATISTICS = ‘TRUE’

2) To put an event statement for getting bind variables in the trace file set the profile option as follows

BEGIN FND_CTL.FND_SESS_CTL(”,”,”,”,”,’ALTER SESSION SET EVENTS =””””” 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 ”””’); END;

3) To set the logging on,

set the profile options (Initialization SQL statement – custom) as follows:
BEGIN FND_CTL.FND_SESS_CTL(”,”,”,”,’LOG’,”); END;

begin fnd_ctl.fnd_sess_ctl(”,”,’TRUE’,'TRUE’,”, ‘ALTER SESSION SET EVENTS=”””””10046 TRACE NAME CONTEXT FOREVER, LEVEL 12”””””TRACEFILE_IDENTIFIER=”””””JIL”””’);end;

For this option the log table is FND_TRACE_LOG, This table should be purged periodically.
If this LOG mode is set then then following information will be inserted in FND_TRACE_LOG table (every time when fnd_global.initialize procedure is called):
user_name,
sysdate,
conc_request_id,
oltp_opt_mode, (optimizer mode for online transactions)
conc_opt_mode, (optimizer mode for concurrent programs)
conc_program_name,
user_conc_program_name,
resp_name,
application_short_name

Here I am setting the custom sql for sysadmin for test purposes.

WARNINGS
——–
1) Do not ever set profile option ‘Initialization SQL Statement – Oracle’ instead of ‘Initialization SQL Statement – Custom’.
‘Initialization SQL Statement – Oracle’ (FND_APPS_INIT_SQL) profile option is used by Oracle Applications to add application-specific initialization code.

This profile option is set at the application level only, and will only be executed for responsibilities owned by that application. This profile option and its value settings are delivered as seed data, and must not be modified.

2) Setting of profile option ‘Initialization SQL Statement – Custom’ can effect on the operation of the whole Applications. As far as possible set this profile option only on USER level. Syntax error can cause that users can not login to the Applications or cannot run any concurrent programs. Please make sure syntax is correct. If you set this profile option on site level and a syntax error is in the profile value then it would cause that nobody can login to the applications.

In this situation the error message is something similar: ORA-20001: Oracle error -20001: ORA-20001: -: While executing SQL in profile FND_INIT_SQL:BEGIN FND_CTL.FND_SESS_CTL(’RUL’,”,”,”,’LOG’,”)has been detected in FND_GLOBAL.INITIALIZE.

If you cannot login to Applications with any apps user then you can correct this in SQL*Plus by selecting and updating the profile_option_value:

You can correct the syntax error or can set profile_option_value to NULL and then define it in the Applications again (preferred way):

How to check the existing values of ‘Initialization SQL Statement -Custom’?

Following script used to set the values using sqlplus instead of sys.adm -> profile value.
The script can be downloaded here