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:

    l_del_rows_cnt NUMBER;
l_del_rows_cnt := fnd_log_admin.delete_all;
DBMS_OUTPUT.PUT_LINE(l_del_rows_cnt || ' rows deleted');