Common Debugging Framework in Oracle Application
August 27, 2011 Leave a comment
Debugging plays a very crucial rule when you develop something and in oracle application also it is no different. Prior to Oracle E-Business Suite 11i, each module used in Oracle EBS had its own debugging methodology. However, with the Common Debugging Framework initiative, Oracle has introduced a common set of profile options and tables that can be used to debug any application across all technologies used in oracle apps..
Starting in 11.5.10, FND has incorporated a debugging technique to enable debug messages to get stored into the table FND_LOG_MESSAGES. This method was introduced in 11.5.10 and it is available in subsequent releases.
There are few profile options to enable and retrieve the debug messages. Here are those profile options.
Profile Name | Suggested value | Comments |
FND: Debug Log Enabled | YES | This turns the debugging feature on |
FND: Debug Log Filename | NULL | Use when you want debug messages to get stored to a file |
FND: Debug Log Level | STATEMENT | Following are options listed from least to most detailed debugging : Unexpected, Error, Exception, Event, Procedure, Statement |
FND: Debug Log Module | % | Indicate what modules to debug. You can use something like ‘ar%’ or even ‘%arp_rounding%’ to limit modules debugged |
Sample setting to debug everything:
FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module %
Sample setting to debug ONLY Receivables:
FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module ar%
Sample Program:
Create or replace PACKAGE BODY xx_debug_pkg AS g_level_statement CONSTANT NUMBER := fnd_log.level_statement; g_level_procedure CONSTANT NUMBER := fnd_log.level_procedure; g_level_event CONSTANT NUMBER := fnd_log.level_event; g_level_exception CONSTANT NUMBER := fnd_log.level_exception; g_level_error CONSTANT NUMBER := fnd_log.level_error; g_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected; g_default_module CONSTANT VARCHAR2 (240) := 'Any_Package_Name'; g_level_log_disabled CONSTANT NUMBER := 99; g_log_level NUMBER; g_log_enabled BOOLEAN; PROCEDURE DEBUG ( p_log_level IN NUMBER, p_module IN VARCHAR2, p_message IN VARCHAR2, p_request_id IN NUMBER ) IS BEGIN fnd_profile.put ('AFLOG_MODULE', g_default_module); IF ((p_module IS NULL) OR (p_message IS NULL)) THEN fnd_file.put_line (fnd_file.LOG, 'Error in package:' || g_default_module || ' , module : debug :Parameters p_module and p_message cant be null' ); ELSE BEGIN IF (NVL ((fnd_profile.VALUE ('AFLOG_ENABLED')), 'N') = 'Y') THEN IF (p_message IS NOT NULL AND p_log_level >= g_log_level) THEN fnd_log_repository.init; fnd_log.STRING (p_log_level, g_default_module, p_message); END IF; END IF; EXCEPTION WHEN OTHERS THEN fnd_file.put_line (fnd_file.LOG, 'Error in package: ' || g_default_module || ', module : debug, at check point 1 :' || SQLERRM ); END; END IF; EXCEPTION WHEN OTHERS THEN fnd_file.put_line (fnd_file.LOG, 'Error in package:' || g_default_module || ' , module : debug, at check point 2 :' || SQLERRM ); END DEBUG; END xx_debug_pkg;
Debugging an API from SQL*Plus
You can enable FND logging for just one single PL/SQL API. Here is how we can do it from SQL*Plus:
1. From SQL*Plus, issue the following:
fnd_global.apps_initialize(fnd_user_id, fnd_resp_id, fnd_appl_id); fnd_profile.put('AFLOG_ENABLED', 'Y'); fnd_profile.put('AFLOG_MODULE', '%'); fnd_profile.put('AFLOG_LEVEL','1'); -- Level 1 is Statement Level fnd_log_repository.init;
2. Call the desired API.
3. Call step 1 again, but this time set AFLOG_ENABLED to N.
Since the debugging routine will start writing messages to the table, we want to know which messages pertain to our test. If you are tracking the debug messages for a concurrent request, note down the Concurrent Request id. Otherwise, note down current max value of log sequence retrieved as follows:
SELECT MAX(LOG_SEQUENCE) FROM FND_LOG_MESSAGES;
If you are debugging a concurrent process:
SELECT log.module , log.message_text message FROM fnd_log_messages log, fnd_log_transaction_context con WHERE con.transaction_id = < request_id > AND con.transaction_type = 'REQUEST' AND con.transaction_context_id = log.transaction_context_id ORDER BY log.log_sequence;
Otherwise use this:
SELECT module, message_text FROM fnd_log_messages WHERE log_sequence > &max_log_from_step2 ORDER BY log_sequence;
Debugging OA pages
a. Enable the profile option: FND: Debug Log Enabled — Set to Yes
b. Enable the profile option: FND: Debug Log Level — Set to Statement level
c. Add the below piece of code in your OA page
boolean isLoggingEnabled = pageContext.isLoggingEnabled(OAFwkConstants.STATEMENT); if (isLoggingEnabled) { pageContext.writeDiagnostics(this, "your log statement", OAFwkConstants.STATEMENT); }
To see log stmt on browser append below string to browser URL and click on enter
&aflog_level=statement
Reference MOS Notes:
- How to enable and retrieve FND debug log messages [ID 433199.1]
- How Can Trace and Debug Be Turned On For A Concurrent Request? [ID 759389.1]
- How to Collect an FND Diagnostics Trace (aka FND: Debug) [ID 372209.1]
- How to Obtain Debug Log in R12 [ID 787727.1]