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

Some years ago I posted a blog on the AMIS technology blog describing a trace method of Self Service applications within Oracle EBS 11i. The main difference in this post is that the insert into the table fnd_profile_option_values now has one extra field inserted. This change has been introduced somewhere in 11.5.9. The scripts below can be used in higher versions, the post from the past at the AMIS technolog blog can be used in older versions.

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
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, null
from fnd_profile_options, fnd_user
where profile_option_name = ‘FND_INIT_SQL’
and user_name = ‘&un’
/

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 = ‘&un’)
and profile_option_id = (select profile_option_id
                         from fnd_profile_options
                         where profile_option_name = ‘FND_INIT_SQL’) —> What is the name in Applications.
/
select ‘Type commit; or exit; to delete the inserted profile option value’
from dual;

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

  • start the 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.
  • stop in the sqlplus session the tracing for the 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)
  • Be aware that using the profile option will turn on the trace for *ALL* processes executing for that user, including any scheduled concurrent processes.

To identify the tracefiles created in the user dump destination better you can add a ‘tracefile_identifier’ to the SQL. In that case use the deftrace command below:

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

The above procedure I needed for a customer I was working for recently. I hope if you have comments you will not hesitate to comment.

Regards, Gerwin

One thought on “SQL Tracing in Self Service Applications (CRM) within Oracle Applications 11i

  1. Awsomeness. Thank you.

    Is there anyway from SQL to monitor the use of Self Service? For example, fnd_login_responsibilities only tracks form responsibility logins in 11.5.10.2.

    Thanks again.
    Logan 5

Leave a Reply to Logan 5 Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>