Using Profile Option 'Initialization SQL Statement - Custom'

 'Initialization SQL Statement - Custom' (FND_INIT_SQL) profile option

---------------------------------------------------------------------

Using the profile option 'Initialization SQL Statement - Custom', one 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_infoon session startup.

Query this to check the location of Trace file:

select * from V$PARAMETER

WHERE NAME LIKE 'user_dump_dest'

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.

The profile option can be set 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 all the quotes are correct.

Examples:

1) To enable SQL trace for the session, 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;

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


4) Trace can be enabled with trace identifier in order to specify a file name
(Here: IDENTIFIER)
Example:

BEGIN FND_CTL.FND_SESS_CTL ('','','TRUE','TRUE','LOG',
'ALTER SESSION SET TRACEFILE_IDENTIFIER =''IDENTIFIER'
' EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''');
END;

<span style="font-family: Courier New,Courier,mono;" data-mce-style="font-family: Courier New,Courier,mono;">WARNINGS</span>

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 issued 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:

SELECT val.level_id, val.level_value, val.profile_option_id,
val.profile_option_value
FROM fnd_profile_options opt, fnd_profile_option_values val
WHERE opt.profile_option_name = 'FND_INIT_SQL' AND
opt.profile_option_id = val.profile_option_id;

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

UPDATE fnd_profile_option_values
SET profile_option_value = NULL
WHERE profile_option_id = AND
level_id = AND
level_value = ;

Diagnostics & Utilities Community: