Home » Oracle » Oracle Ebuisness Suite » Initialization SQL Statement – Custom” (FND_INIT_SQL) profile option

Initialization SQL Statement – Custom” (FND_INIT_SQL) profile option

Profile Option: ‘Initialization SQL Statement – Custom’ provides a profile option that allows to execute ‘custom’ code at the beginning of every database session. These sessions can be linked to an Oracle application Form, a Concurrent Program, Self Service Page or any other piece of code that requires accessing the database. The most common use of this profile option is to generate detailed raw SQL Trace files including the values of the bind variables used by SQL statements.This is a good way to enable trace for a user in oracle apps

It is recommended to set this at User level only. We should not be setting it up at Site level. If set at site level in a wrong way, this will block all users from login to the system

This can be also to set different database initilization parameter for the user , responsibility.

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.

How to Set this Profile

(1)Log onto System Administrator Responsibility
(2)Navigate: Profile > System
(3)Search for the profile option Initialization SQL Statement – Custom at USER level.
(4)Set the value to below. This will enable trace level 12 for the session


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

Here USERID is to substituted with the username where profile is being set

See also  Opmnctl command every administrator should know

Once the Profile is set, you can execute the process whose trace is required and then you can again disabled the trace by removing this option

How to set this Profile from Backend

This can also be set from backend using below PLSQL block

Set serveroutput on
DECLARE
l_ret boolean;
l_user_id number;
BEGIN
select user_id
into l_user_id
from fnd_user
where user_name = '&&USER_NAME';
l_ret := fnd_profile.SAVE(X_NAME => 'FND_INIT_SQL',
X_VALUE => 'BEGIN FND_CTL.FND_SESS_CTL('''','''','''', ''TRUE'','''',''ALTER SESSION SET TRACEFILE_IDENTIFIER=''||''''''''||''&&USER_NAME'' ||''''''''||'' EVENTS =''||''''''''||'' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 ''||''''''''); END;',
X_LEVEL_NAME => 'USER',
X_LEVEL_VALUE => l_user_id);
commit;
dbms_output.put_line('Profile has updated successfully');
EXCEPTION
when others then
dbms_output.put_line('Failed to update the profile: '||sqlerrm);
END;

How to disable this profile from Backend

You can disable using below PLSQL block

DECLARE
l_ret boolean;
l_user_id number;
BEGIN
select user_id
into l_user_id
from fnd_user
where user_name = '&USER_NAME';
l_ret := fnd_profile.DELETE(X_NAME => 'FND_INIT_SQL',
X_LEVEL_NAME => 'USER',
X_LEVEL_VALUE => l_user_id);
commit;
dbms_output.put_line('Profile has erased successfully');
EXCEPTION
when others then
dbms_output.put_line('Failed to erase the profile: '||sqlerrm);
END;

Troubleshooting Issues with the Profile FND_INIT_SQL

Some time many user might be getting below error while logging to Applications

ORA-20001: Oracle error -20001: ORA-20001: -: While executing SQL in profile
FND_INIT_SQL:BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER S has been
detected in FND_GLOBAL.INITIALIZE. ORA-20001: Oracle error -20001: ORA-20001: -:
While executing SQL in profile FND_INIT_SQL:BEGIN FND_CTL.FND_SESS_CTL('','',
'', 'TRUE','','ALTER S has been detected in FND_GLOBAL.INITIALIZE.

This happen if the profile is not set in correct manner.

In this case, we can run below query to check this profile at all the levels in the applications

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
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(p.profile_option_name) like '%FND_INIT_SQL%'
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;

Now we can null the problematic level and solve the issue.If it is site level which is causing issue. we can delete it by using the query

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

COMMIT;

Similar statement can be executed for responsibility and user level also

See also  how to check the restore point in Oracle

Hope you like article on how to enable trace for a user in oracle apps

Related Articles
SQL trace : SQL trace or 10046 event is being used to trace the session activity in Oracle. The output generated by SQL trace is formatted using trcsess, tkprof utility
Autotrace in oracle : Autotrace in oracle , What is autotrace,how to setup it,Understanding Autotrace Output, Autotrace options, statistics displayed by autotrace
sql tuning advisor : How to run sql tuning advisor for sql_id in the Cursor cache, how is the sql tuning task created and executed to get the recommendation
Hanganalyze : check out how to take system state dump in oracle, hanganalyze in oracle , what is v$wait_chains and how it can help find the blocking
How to generate FRD trace in Oracle Apps : How to generate Forms runtime diagonistics trace in oracle apps 11i/R12

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top