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
SQL TRACE and TKPROF
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
WHERE user_id = 209122 /*your FND_USER user_id here*/
ORDER BY log_sequence DESC
Concurrent Program: “Purge Debug Log and System Alerts”
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:
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
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:
Form Level Items
Navigation To Examine Utility
You can also switch off your custom code
Help> Menu > Diagnostics > Custom Code
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:
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.