Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Monday, 18 April 2016

Oracle Form Debugging

Debugging Forms
The biggest challenge for any form application is to debug it. There are many ways to debug Oracle Apps Forms. Few of them are list as below

FND Logging
Debug Messages
Diagnostics Utility

FND Logging
We should design and build a custom extensions in a manner that can easily be debugged. This can be done by calling Oracle delivered API’s in your custom code. The API is FND_LOG.
The debug messages are stored in a table called FND_LOG_MESSAGES
A program written in any technology, either form, or report, or PL/SQL or java concurrent program or OAF.

FND Logging: Setups

To enable logging, there are few profiles which are required to enable. These are:
FND: Debug Log Level
FND: Debug Log Enabled
FND: Debug Log Module     
The various possible values available for FND: Debug Log Level are:

FND Logging: Setups
Oracle recommends to set this to "Statement" level as it extracts debug messages at all levels, in one glance.

FND: Debug Log Enabled: Set this profile to YES. If it is yes, then only system will store message.

FND: Debug Log Module: Set this profile which is causing this proble. Oracle recommends to set this value to “%” as we are not sure which module is causing this error. For example, set this to po%, if you know for sure that the error was caused by code written in po module. However po code might be internally calling hr code which might intern be calling fnd code. Hence it’s best to set this profile value as %.

Creating LOG Messages
You can invoke standard API to create error/debug messages
FND_LOG.STRING ( log_level => fnd_log.level_statement
                                     module    => 'xxpo.packagename.procedurename'
                                     message   => 'debug message here‘
This procedure uses pragma AUTONOMOUS_TRANSACTION with a commit.
Hence your debug messages will not be lost despite a rollback in parent session.

Retrieving and Purging LOG Messages
You can retrieve the error/debug messages from table FND_LOG_MESSAGES
   FROM   fnd_log_messages
 WHERE  user_id = 209122 /*your FND_USER user_id here*/
ORDER BY log_sequence DESC
Concurrent Program: “Purge Debug Log and System Alerts”

Debug Messages
Use FND_MESSAGE API to get any debug messages. Its one procedure Debug immediately displays the string passed to it as input parameter.
fnd_message.debug(‘Your Debug msg here’);
This is very useful at the time of development of forms

Oracle has provided a very useful utility in Oracle Application to find the value of variables which are set by form in runtime. These variables can be:
Examine Utility
Custom Code

Diagnostics: Setups
Oracle has provided a very useful utility called Diagnostics in Oracle Application which can be used to debug the code. There are few profile which must be set in enable this utility. These are:
Hide Diagnostics menu entry -> If the profile option is set as ‘No’, Only then it will appear in the help menu
Utilities: Diagnostics -> If set to ‘Yes’, No APPS schema password is required otherwise APPS password required to use any of its feature

Examine Utility
Oracle has provided a very useful utility with-in Diagnostics to find the value of variables which are set by form in runtime. These variables can be:
System Variables
Environment Variables
Profile Values
Global Variables
Form Level Items

Navigation To Examine Utility


Custom Code
You can also switch off your custom code

Help> Menu > Diagnostics > Custom Code

Last Query
You can use system variable LAST_QUERY to retrieve the last query which form has fired. This SQL will have all the bind variables embedded within it. You can run it as it is.
After having clicked on examine, enter values as:Block = SYSTEM and Field  = LAST_QUERY
SYSTEM.LAST_QUERY will not display:

LOV Queries
Post Query SQL’s
Cursors in Form Triggers/Attached pll’s

SQL Trace and Tkproof

The SQL Trace facility is very basic debugging diagnostic tool that can help you monitor as well as tune applications running against the Oracle Apps Forms.
The SQL trace files produced in raw form. This can be translated by the tkprof (transient kernel profiler) utility into a more human readable form.

Navigation to enable SQL Trace:
Help > Diagnostics >Trace > Trace with Binds and Waits
(Set trace with binds and waits, using help menu. Using the “binds” option, you will get to see value of the variables in SQL.)
System will give you location where trace file will be created. It always created at database tier.

Share this article :


  1. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check

  2. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check for Never Before Offers and Discount Coupon Codes.