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. Continue reading