Common Debugging Framework in Oracle Application


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]