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

3

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.

Share.

About Author

3 Comments

  1. Gerwin Hendriksen on

    Thanks for your comments John. I only like to add that in most situations you will create a new test Apps user to do the specific job, you want to investigate. In that way you will not get the issue you describe.

  2. Nice one. However, I would add a ‘tracefile_identifier’ to this SQL so that the tracefile is easily identified in the udump directory. Thus the deftrace command would be:

    BEGIN FND_CTL.FND_SESS_CTL(”,”,”,”,”,’ALTER SESSION SET EVENTS=’||””||’ 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 ‘||””|| ‘ TRACEFILE_IDENTIFIER = ‘ || ”” || ” || ””); END;

    Also, be aware that this will turn on the trace for *ALL* processes executing for that user, including any scheduled concurrent processes…

    John Kanagaraj