SQL Tracing in Self Service Applications (CRM) within Oracle Applications 11i

In a lot of cases it is very interesting to be able to trace Self Service Applications (CRM) in Oracle Applications 11i. Most of the time it is very hard to understand what is going on in such a session and in that case it might be very handy to get the SQL behind it. To do this there is a very nice not really known feature of the profile option “Initialization SQL Statement – Custom”. With this profile option it is possible to trace any session of a given Apps user on any level. To ensure you don’t end up with a hanging apps login (quote’s should be placed the right place) you can use the following scripts just on the SQL plus prompt to activate and stop the tracing.

Start:

whenever sqlerror exit failure rollback;

set heading off
REM define un = &username
define deftrace=’begin fnd_ctl.fnd_sess_ctl(””””,””””,””TRUE””,””TRUE””,””LOG””, ””ALTER SESSION SET EVENTS=””||””””””””||””10046 TRACE NAME CONTEXT FOREVER, LEVEL 12””||””””””””);end;’

select ‘Inserting profile option value’
from dual;

insert into fnd_profile_option_values
select 0,profile_option_id, 10004, user_id,sysdate,0,sysdate,0,0, ‘&deftrace’, null
from fnd_profile_options, fnd_user
where profile_option_name = ‘FND_INIT_SQL’
and user_name = ‘&APPS_USER’
/

commit;

Stop:

select ‘Press ENTER to disable trace for’, ‘&un’
from dual;

select ‘&ENTER’
from dual;

delete fnd_profile_option_values
where application_id=0 and level_id=10004
and level_value = (select user_id
from fnd_user
where user_name = ‘&APPS_USER’)
and profile_option_id = (select profile_option_id
from fnd_profile_options
where profile_option_name = ‘FND_INIT_SQL’)
/

commit:

Just to use the scripts the following steps and instructions should be used:

• run the start script in sqlplus and provide the application username to be traced in Self Service and give an Enter (this session stays open now)
• Login with the application user, and reproduce the to be traced functions.
• Logout with your application user.
• run the stop script in the sqlplus session for the traced Oracle Applications user.

* Important for the script:

• Check if define deftrace is one line ‘…..’
• Level 12 (waits&binds) can be changed to 8(waits), 4(binds) and 1(SQL_TRACE)

If you might have any questions, don’t hesitate to contact me.