PLSQLLOG Technical overview

PLSQLLOG is a logging framework. It can be used to log text messages to a table and/or file and/or sqlplus session (dbms_output). It provides logging procedures for different logging levels (debug, info, warn, error, fatal). Logging can be customized using a different character string for each logging purpose. This character string is stored together with the logging level, logging target and the target name in a configuration table called PLSQLLOG_CONFIGURATION.

The implementation of the logging framework uses Oracle® Advanced Queuing (AQ). AQ is use to decouple the producer of log messages from the consumer. Therefore the producer "only" needs to put the message in a queue (and print to the console - if configured) while the consumer executes the time consuming process of writing to tables and/or files.

The process of logging a single message is graphically shown in the following picture:

Step 1: A user session calls PLSQLLOG.LOGGER.LOG_INFO('An information message')
Step 2: If the log target 'console' is configured the message will be printed using DBMS_OUTPUT.
Step 3: The message is inserted into the logging queue.
Step 4: The callback is executed. It retrieves all messages currently in the queue and processes them one by one.
Step 5: Depending on the configuration the message is logged into a table and/or file.

Please note:
Although it is possible to log to the alert log, it is generally not recommanded. First because the functionality is not documented by Oracle® and second because you pollute the alert log.

On the other hand there may be situations you need to log fatal errors to the alert log. All internal errors are written to the alert log because this is the only file that always exists - untill now.

Weak points:
 - The content of the log tables is not managed by the framework, e.g. delete old log entries.
 - The content of the files is not managed by the framework, e.g. delete the log files, create new log files and saved old ones, etc.