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

SQL TRACE and TKPROF

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:

LEVEL_UNEXPECTED

LEVEL_ERROR

LEVEL_EXCEPTION

LEVEL_EVENT

LEVEL_PROCEDURE

LEVEL_STATEMENT

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

SELECT    *

   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

Diagnostics

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

Trace

Properties

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

Parameters

Form Level Items

Navigation To Examine Utility

Help->Menu->Diagnostics->Examine

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.