how to debug oracle

There are three ways of debugging the Applications and they are:

• Debug Log
• Trace and TkProf
• FRD Log

Debug Log Messages: This is the best method to debug.

Step 1:
The first thing in this process is to enable the debug messages. This can be achieved by setting the following Profile Options at the User Level. 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:
After setting the Profile options, the next step is to find the initial value from where our debugging routine will start. To know this value:

SELECT MAX(LOG_SEQUENCE)
FROM FND_LOG_MESSAGES;

Step 3:
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, 

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.
Sample debug file:

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.