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