Logging in OAF Pages – A Technical Note!


The Logging Framework in Oracle Apps provides the ability to store and retrieve log messages for debugging, error reporting, and alerting purposes. We can use it for any OAF Page development and Customization.

Using Oracle AOL Profile Options to Configure Logging

You can configure logging by setting Oracle Application Object Library (FND) profile options.

The available levels are Site, Application, Responsibility, and User. User settings override Responsibility settings, Responsibility settings override Application settings, and Application settings override Site settings.

Here is a summary of the impacts of the different profile option levels:

  • User: Affects only the given user.
  • Application: Affects all users for the specific application.
  • Responsibility: Affects all users in any application for that responsibility.
  • Site: Affects all users, applications, and responsibilities.

Note: When setting up logging at the Site level, Oracle strongly recommend that you set the logging level to UNEXPECTED. ERROR or EXCEPTION. Also remember to return the profiles to their usual values after debugging has been completed.

Where is the debug message stored, once the logging is turned on?

Debug messages are stored in a table called FND_LOG_MESSAGES.

If you want to have logging from your concurrent program, you can write as below in your PL/SQL Code.

fnd_log.STRING(log_level => fnd_log.level_statement
                             ,module => 'xxpcm.packagename.procedurename'
                           ,message => 'You debug message here');

From OAF java code, you can write like

pageContext.writeDiagnostics(“xxpcm.oracle.apps.pa.ci.webui”, “Your debug message here”, 1);

Using Logging to Screen

In addition to the above methods where log messages are written to a file or the database, Logging to Screen provides:

  • The ability to enable logging on a per HTTP request or per HTTP session basis.
  • Dynamic configuration which does not require restarting any servers or changing any log profiles.
  • A convenient lightweight mechanism to diagnose performance issues. Each message is timestamped to the millisecond.

If Logging to Screen is enabled, then the Java log messages generated for a particular HTTP Request-Response are buffered in memory and appended to the end of the generated HTML page. However this feature does not affect any existing configurations of file or database logging. File or database logging continues to behave per the configured middle tier log properties and/or log profile values.

Note that this mechanism currently provides only Java layer messages. Regular file or database logging should be used if messages from other layers (e.g., PL/SQL) are needed.

Enabling Logging to Screen in Oracle Application Framework Pages

For security reasons, oracle has made this feature only accessible if the “FND: Diagnostics” Profile is set to “Yes“.

1] First go to the page and click the Diagnostics button.

After you select the Diagnostics button to navigate to the Diagnostics page, you can select:

  • Show Log – It directs you to the Oracle Applications Manager where you can view a “snapshot” of your Oracle E-Business Suite system.
  • Show Log on Screen – It allows you to specify a log level and display Java log messages for a particular HTTP Request-Response at the end of the current page.
  • Set Trace Level – It displays the Set Trace page where you can specify the level of information to collect in a database trace.
  • Show Pool Monitor – It displays the Application Pool Monitor where you view different aspects of runtime and configuration information about the JVM.

2] Select Show Log to Screen from the drop-down list.

Caution: If you set the default site-level logging level to STATEMENT or PROCEDURE, a decrease in system performance could result. Under that configuration, the large amount of generated log messages might significantly slow down the system. Furthermore, if the site-level logging level is set to a low severity for a long time, then the FND_LOG_MESSAGES table could potentially run out of space.

For High Volumes

For high load, high volume scenarios, you can log middle-tier messages to a local file, which is faster than logging to a remote database.

Purging Log Messages

You should periodically delete old log messages to account for the space limitations of the database table. In addition, you should periodically rotate log files.

There are several ways to purge log messages. They are described below:

1] Using a Concurrent Program

The concurrent program “Purge Debug Log and System Alerts” (Short name: FNDLGPRG) is the recommended way to purge messages. This program purges all messages up to the specified date, except messages for active transactions (new or open alerts, active ICX sessions, concurrent requests, and so on). This program is by default scheduled to run daily and purge messages older than 7 days. Internally this concurrent program invokes the FND_LOG_ADMIN APIs, which are described below.

2] Using PL/SQL

You can use the FND_LOG_ADMIN PL/SQL package to delete log messages.

fnd_log_admin apis:

Name Type Description
delete_by_date_i Procedure This routine is used as a concurrent program. Nobody besides the concurrent manager should call it.
delete_by_user Function Delete all log messages for a particular user
delete_by_session Function Delete all log messages for a particular session
delete_by_user_session Function Delete all log messages for that match both user and session
delete_by_module Function Delete all log messages that are “like” module
delete_by_date_range Function Delete all messages between the specified dates. passing null means unlimited; null for both deletes all rows.
delete_by_max_level Function Deletes messages at level and all levels below.
delete_all Function Delete all messages
delete_by_sequence Function Delete all log messages based on sequenceid

For example:

SET SERVEROUTPUT ON
declare
    l_del_rows_cnt NUMBER;
BEGIN
l_del_rows_cnt := fnd_log_admin.delete_all;
DBMS_OUTPUT.PUT_LINE(l_del_rows_cnt || ' rows deleted');
END;
Advertisements

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]