How to ENABLEND this profile from Backend
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;