Log4plsql: Open-Source Tool for PL/SQL Logging
By Guillaume Moulard
OTN Member since 2000

Logging, an important part of the development lifecycle, is often considered too tedious—but it needn't be.

The implementation of logs is a significant element in development strategy: exhaustive yet concise logs are required to enable code debugging and the tracing of functional events. At the same time, however, in this era of diminishing resources it is necessary to minimize the workload generated by logging.

In this article, I'll explain the usefulness of PL/SQL logging and introduce the open-source Log4plsql tool—an extension of the popular Apache Log4j Java logging project—which offers all the tools and methods required for the management of logs and traces in any kind of PL/SQL code. Furthermore, it's completely free.

The Importance of Logging

Programmers use logs in several ways to help them develop, integrate, and manage their code:

• During code development, the programmer must be able to trace all the code operations, data, and events. The goal is to obtain all internal information about the code.
• When code development is complete, many programmers leave traces on the input data and on the code results. Furthermore, they save in the log files all corrupted and unscheduled events that have been detected during code execution. Functional trace generation may also be a routine step.
• During the integration and testing phases, logs help indicate the importance of a single element across the entire architecture. Typically, logs about unforeseen events and functional traces are most useful during these periods.
• After deployment, logs allow developers to follow-up and verify the original application work, as well as provide statistics about how the production application is being used. Usually functional traces are most interesting here; in some applications, it is also useful to understand when the system launches, quits, or crashes.

Your first step, of course, is to download and install the Log4plsql package at http://prdownloads.sourceforge.net/log4plsql/Log4plsql.zip. Before using the Log4j features in Log4plsql, you'll need to configure the Log4jBackgroundProcess constant. (See the installation guide for details.) The entire process should take 10 minutes.

The standard way to configure Log4plsql is to make direct calls to log processes at the specified level of activity. (See Table 1.) By default, logs are stored in the TLOG table. You can read them through the VLOG view. For example:

Fatal	The FATAL level designates very severe error events that will presumably lead the application to abort. → plog.fatal('Text');
Error	The ERROR level designates error events that might still allow the application to continue running. → plog.error('Text');
Warning	The WARN level designates potentially harmful situations. → plog.warn('Text');
Info	The INFO level designates informational messages that highlight the progress of the application at a coarse-grained level. → plog.info('Text');
Debug	The DEBUG Level designates fine-grained informational events that are most useful to debug an application. → plog.debug('Text');

Begin 
    PLOG.error ('your error message');
End;
/

SQL> select * from vlog;

LOG
--------------------------------------------------------------------------------
 [08/08 19:14:06: 05][ERROR  ][SCOTT][block][yours error message]

SQL>

Global Settings

The PLOG package specification contains tree constants that let you modify the standard behavior of Log4plsql:

• DEFAULT_LEVEL CONSTANT TLOG.LLEVEL%TYPE := LERROR;
  By default, Log4plsql stores only logs with a severity higher or equal to ERROR. This level corresponds to a normal use of logs in production. But during development phase, the DEBUG mode is more appropriate (cf pslog.sql → line 89).
• DEFAULT_LOG_OUT_TRANS CONSTANT BOOLEAN := TRUE;
  By default, traces are not canceled during a rollback. If you want to cancel them, put the previous setting to FALSE (cf pslog.sql → line 92).
• DEFAULT_USE_LOG4J CONSTANT BOOLEAN := FALSE;
  Log4jBackgroundProcess is a small Java application that can be started in room or remote. It uses an XML setting file, which has two important pieces of information: the database connection and the Log4j setting file.

By default, you are not compelled to use the Log4jBackgroundProcess. Before modifying this constant, you must ensure that the Log4jBackgroundProcess is launched and that it is listening to the database (cf pslog.sql → line 95).

Customizing Contexts

All procedures and Log4plsql features can have a context as argument. With contexts, you can completely modify Log4plsql behavior. In the most complex case, you can have different contexts: one allowing INFO-type messages that can be deleted during a transactional rollback (pCTX1), and another allowing all logs types to be stored independently of the transactions in a text file (pCTX2). For example:

Listing
Declare
   pCTX1 PLOG.LOG_CTX := PLOG.init (pSECTION   => 'firstSection', 
                                    pLEVEL     => PLOG.LINFO, 
                                    pLOG4J     => FALSE, 
                                    pOUT_TRANS => FALSE);

   pCTX2 PLOG.LOG_CTX := PLOG.init (pLEVEL     => PLOG.LALL, 
                                    pLOG4J     => TRUE, 
                                    pOUT_TRANS => FALSE);
begin
     PLOG.info  (pCTX1, 'newInformationMessage');
     PLOG.debug (pCTX2, 'debug message for Log4j');
end;
/

In general, contexts should be only used to customize section management. Other settings should be common to the whole application, so you can modify them directly in the PLOG package specification code.

Customizing the Section Tree

Localization is among the most important information you can save in your application logs. By default, for every log, Log4plsql computes the call stack of the PL/SQL. For every call level, the user and the code name are saved:

 
block.SCOTT.PTSEC.SCOTT.FUNCTSEC->error in funcTSec

In this example, an anonymous block launches PTSEC package owned by scott user. This package calls the FUNCTSEC function. This function logs and displays the message: 'error in funcTSec'.

This behavior can be customized regarding to the application needs. You can create a tree with specific sections linked to a context:

pCTX PLOG.LOG_CTX := PLOG.init ('initApplication');

Then, it is possible to add or delete sections for a context:

PLOG.SetBeginSection (pCTX, 'connectionPhase');

or:

PLOG.SetEndSection (pCTX, 'connectionPhase');

In this sample, if a log is used between the SetBeginSection and the SetEndSection, it would have the section initApplication.connectionPhase.

Customizing the Logging Level

It is possible within a context to force the minimum log level. It can be useful, for example, to trace specific functional information messages.

To do so, it is necessary to initialize a context with the chosen level. When the application is running, you can always use the functions setLevel and getLevel to change the level according to changing needs.

By using the functionalities of Log4j through Log4jBackgroundProcess, it is possible adjust the configuration much more finely: There can be different levels of log for each section and each destination. In this case, all the logs larger or equal to the minimum level are sent via DBMS_PIPE to Log4jBackgroundProcess, which uses the large functionalities of Log4j to filter the levels and the destinations.

Customizing Transactional Limits

There are two ways to log messages outside the current transaction via Log4jBackgroundProcess or with the AUTONOMOUS_TRANSACTION feature.

By default, logs are stored in TLOG table outside the current transaction. During the use of Log4jBackgroundProcess, there is no need to change any settings as logs are always outside the current transaction. For the AUTONOMOUS_TRANSACTION feature, you need to initialize a context by putting setting the pOUT_TRANS value to false. In this case, if the transaction ends with a rollback, logs are cleaned in the TLOG table. This approach would be useful, for example, if your application were to give you the message 'bill 123 validate' even though the validation transaction is wrong. In that case, the message would have to be deleted.

Next Steps Download Log4plsql from sourceforge.net Read Log4j Documentation Visit OTN's PL/SQL Technology Center

For a context, you can modify at any time pOUT_TRANS value by using SetTransationMode and getTransactionMode features in PLOG.

Using Log4j Features

One of the main functionalities of Log4plsql is the use of Log4j via the Log4jBackgroundProcess. Here's how it works: The PLOG package broadcasts the logs to Log4jBackgroundProcess through a DBMS_PIPE. Then the process uses Log4j to route logs regarding settings and layouts (log format) through different Appenders (to the log destination: external file, SMTP, NTEventLog, console, jms, syslog, and so on).

When this process launches, all Log4j functions will be running.

Purging the Database Log Table

It is necessary to purge the log table regularly. To do that, you can use the standard purge process with the maximum date setting in effect.

To delete logs prior to the current week, you can do this:

Declare
    pCTX PLOG.LOG_CTX := PLOG.init;
begin 
    PLOG.purge (pCTX, sysdate - 7 );
end;
/

What's Ahead for Log4plsql

As you can see, Log4plsql offers all the functionality you need to manage your logs.

In its first release, you were able to manage logs and traces in an Oracle table, inside or outside the current transaction. The second release added Log4j features, such as the ability to log to many different destinations (such as external file, mail, console, and SMTP). In the next release (scheduled for January 2004), Log4plsql will add an applicative framework for checking errors, logs, traces and exceptions in PL/SQL code—supporting automatic management of Oracle error log messages, writing of logs and traces in the alert.log and in the Oracle trace files, easy debugging (error stack, call stack, assert, and so on), the addition of custom log level, and more.

For information, new feature needs, comments and remarks, join the Log4plsql-all-info@lists.sourceforge.net mailing list.