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.
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’
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’
select ‘Press ENTER to disable trace for’, ‘&un’
where application_id=0 and level_id=10004
and level_value = (select user_id
where user_name = ‘&APPS_USER’)
and profile_option_id = (select profile_option_id
where profile_option_name = ‘FND_INIT_SQL’)
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.
- AMIS Query on Oracle Enterprise Service Bus, Oracle WebServices Manager, SOA & BPEL. …and not about JBoss
- Oracle Enterprise Service Bus (ESB) – A First Impression (and it's not bad at all!)
- The Future of Oracle Applications – John Wookey at OOW 2006
- Configuring Oracle Enterprise Service and JDeveloper 10.1.3.1 on Windows – Create a JDeveloper connection to the ESB
- Eating your own dogfood – use of Oracle Development tools within the Oracle Applications development group