How to Debug Oracle Applications:

Download PDF

There are three major ways in which one can debug the Oracle Applications. This is applicable to all the released versions in Oracle. Let me give a brief on how to proceed with each of them.

Pre-Requisites:

- You must have the backend access to database and the Unix Box.

- Must be well versed with SQL and PL/SQL

- Must have a good idea about the functional flow of the Applications.

- Access to System Administrator Responsibility in Oracle Applications.

Lets begin with the methods. As already mentioned, there are three ways of debugging the Applications and they are:

1. Debug Log
2. Trace and TkProf
3. FRD Log

Lets discuss the ways in which this can be obtained and how to read them.

Debug Log Messages:

This is one of the most commonly used debug methods. This gives a fair idea of the application as of which package is failing and which value is not getting passed to which parameter and so on.. more importantly, the backend data flow.

Step 1:

========

The first and foremost thing in this process is to enable the debug messages. This can be achieved by setting the following Profile Options at the User Level.

Please note that you can set the profile options only if you have the access to “System Administrator Responsibility”.

FND: Debug Log Enabled YES -- Enables Debugging messages.

FND: Debug Log Filename NULL -- To store the file in an external file, if NULL,

the values are written in a table

FND: Debug Log Level STATEMENT -- Most detailed log

FND: Debug Log Module % -- Application specific, if %, it is enabled for all

applications, else values can be ‘ar%’, ‘ap%’

Step 2:

======

Once the Profile options are set, the next step is to find the initial value from where our debugging routine will start. To identify this value, you will have to login to the Database by using SQL Developer/TOAD and then execute the following Query:

SELECT MAX(LOG_SEQUENCE)
FROM FND_LOG_MESSAGES

This will give a value. Keep this value for future references.

Step 3:

======

Now, try to reproduce the issue and ensure that you try to stay on track in reproducing the issue and leave out extraneous steps so that you don't end up with debug messages that are not relevant to your issue. It is ideal to not have anyone else using the Responsibility you have enabled debug for, so that only messages pertaining to your testcase are picked up.

Step 4:

======

Once the issue is reproduced, stop at the step next to the error, like Click OK to the error and then the next step is to ensure that you retrieve the debug log messages generated by your test case. To retrieve the messages, execute the below query in SQL Developer/TOAD.

SELECT MESSAGE_TEXT, MODULE
FROM FND_LOG_MESSAGES
WHERE LOG_SEQUENCE > &max_log_from_step2
ORDER BY LOG_SEQUENCE;

Now, spool this file to an excel sheet and save it in your system.

Step 5:

======

The next step in the process is to read the retrieved messages. Here is how you can interpret the messages generated.

Here is how a sample debug file looks like:

create_person (+)

LoadDataSources (+)

cacheSetupForOtherEntities (+)

G_DATASOURCE_LOADED = N

l_str = 'USER_ENTERED'

l_str = 'USER_ENTERED'

l_str = 'USER_ENTERED'

l_str = 'USER_ENTERED'

l_str = 'USER_ENTERED'

l_start = 5

i = 1

l_entity_name = 'HZ_PERSON_PROFILES'

l_entity_id = -1

i_content_source_type.COUNT = 0

l_str =

i = 2

l_entity_name = 'HZ_ORGANIZATION_PROFILES'

l_entity_id = -2

i_content_source_type.COUNT = 1

l_str = 'USER_ENTERED'

total = 7

cacheSetupForOtherEntities (-)

p_mixnmatch_enabled = N

p_selected_datasources = 'USER_ENTERED'

LoadDataSources (-)

do_create_party (+)

validate_person (+)

first_name or last_name is mandatory. x_return_status = S

head_of_household_flag in lookup YES/NO. x_return_status = S

deceased_flag in lookup YES/NO. x_return_status = S

after validating date_of_birth and date_of_death against SYSDATE x_return_status = S

(+) after validating the date_of_death and date_of_birth... x_return_status = S

created_by_module is madatory field. x_return_status = S

validate_person (-)

l_mixnmatch_enabled = N

l_selected_datasources = 'USER_ENTERED'

do_create_update_party_only (+)

do_create_party_name (+)

l_party_name = ALLY TIONG

do_create_party_name (-)

Generated person key : TNG.ALC

validate_party (+)

validate_party (-)

hz_parties_pkg.Insert_Row (+)

hz_parties_pkg.Insert_Row (-)

do_create_update_party_only (-)

do_create_party_profile (+)

do_create_person_profile (+)

hz_person_profiles_pkg.Insert_Row (+)

do_create_person_name (+)

do_create_person_name (-)

l_person_name = ALLY TIONG

hz_person_profiles_pkg.Insert_Row (-) x_profile_id = 2783499

do_create_person_profile (-)

do_create_party_profile (-)

do_process_classification (+)

party_id =

do_process_classification (-)

Event Name:oracle.apps.ar.hz.Person.create Event Key:oracle.apps.ar.hz.Person.create382123Maximum nested raise count:100Nested raise count: 0

For every ‘(+)’, the control is getting into that Package and for every “(-)”, the control is coming out of that package. It also depends on what kind of debugging messages are coded in those packages where the control enters and processes the code and then passes to the next level.

P.S: Almost all the packages in Oracle Applications, has debugging messages enabled, however, there are exceptions, where packages may not have debugging enabled, hence, will not be shown in the debug file.

To find the cause of the issue, the first thing to find in a debug file, is the word ‘Exception’. This will be showing the package name where the error occurs. Just, open the package in SQL Developer/TOAD and see the SQL being fired.

Check for the values which are being passed to this SQL and see the exception block of the script. On analyzing further, you will mostly be able to find the value which is not getting passed or the wrong value being passed to this SQL and the reason for the error.

Once this is identified, you have to check the Application Settings and modify it accordingly to have the correct value to be passed to the SQL to prevent it from erroring out.

I will be posting the other two debugging methods in my coming posts.