I highly recommend Tyler Muth's logger.
OraLog Documentation
User Guide
Introduction
How to install
General Concept
Architecture Overview
Database Model
Appender Interface
OraLog Limitations
Introduction
OraLog is a logging framework for Oracle database developers. The main idea of the project was to create an extensible logging framework, easy to understand and easy to adapt to each project logging needs. Another important requirement which was taken into consideration was the need to change the logging properties on the fly that is the logging destination to be easily re-configured without any code changes.
How to install
Installation of OraLog database objects (server side):
- Create a new user which will host the OraLog objects and grant to it the required privileges:
create user oralog identified by xxx default tablespace users quota unlimited on users;
grant create session, create procedure, create table, create public synonym, drop public synonym, create type to oralog;
- Go under the "Management" menu / "Install OraLog Server Side Objects", connect to the previously created user and click the "Install" button.
Important note: OraLog was tested on Oracle 9i and 10g only.
General Concept
Logging something from the PL/SQL code is just a matter of placing an OL.log statement within that module. There are several default logging levels and custom log levels which are defined by the developer according to the project logging strategy.
The log levels of the OraLog framework are:
| Statement | Level value | Constant | Remark |
|---|---|---|---|
| ol.fatal('fatal error') | 10 | ol.FATAL_LEVEL | It creates a fatal log entry. |
| ol.error('upsy... an error has occured!') | 20 | ol.ERROR_LEVEL | It creates an error log entry. |
| ol.warn('hey... may I have your attention pls?') | 30 | ol.WARN_LEVEL | It creates a warning log entry. |
| ol.info('Creating temporary tables...') | 40 | ol.INFO_LEVEL | It creates an info log entry. |
| ol.info('Get the prices with the following SQL-SELECT = {1}', l_sql) | 50 | ol.DEBUG_LEVEL | It creates a debug log entry. |
| ol.log(60, 'The memory dump is {1}', l_dump) | > 50 | It creates a custom log entry. |
Every log message may contain placeholders. A placeholder follows the C# Console.Writeln style except for the fact that are not zero based. A placeholder has the following format: {i}, where i between 1 and 10.
For example, suppose you have a procedure which expects three parameters and you want to log their values. You
can write something like this:
ol.info('param1={1}; param2={2}; param3={3}', param1, param2, param3);
Architecture Overview
OraLog is comprised of two main components:
- the OL CORE which generates log entries and routes them accordingly;
- the OL APPENDERS which are separate modules to which the effective log entries are routed; The OL appenders are the ones who actually do something with the logs. They receive log entries from the OL core and, according to the logic of every appender, they can write into a file, send the logs to a table or, whatever the business decides is useful.
The following figure depicts the whole architecture:
The extensibility of the framework is ensured by adding new appenders which, as soon as they are registered into the OL platform, they can subscribe to logging messages according to some rules defined by the DBA or the person who is in charge with the logging strategy. For example, the DBA may decide to log all messages into a table and, in addition, the error messages to be sent by email to some interested parties. This scenario and, of course, the ones much more complex, can be fulfilled by configuring the proper appenders.
Database model
The database model of the OraLog framework is shown into the following diagram:
The appenders are registered by simply adding them into the LOGAPNMDA table. It's enough just to assign an ID and to tell to the engine what is the PL/SQL package which embeds the appender logic. As soon as the appender is registered it can be configured to subscribe to various log entries. The configuration is done by using the LOGSBS table. If the DEBCND field is defined and the condition evaluates to TRUE then the log is pushed to the corresponding appender otherwise the appender is not invoked.
The LOGALE table is used by the OL CORE to write warnings or error messages. For example, if an appender raises an error the OL CORE will catch it and, instead of breaking the caller procedure, will simply log the error message into the LOGALE table. Likewise, an appender is also allowed to write into this table which is sometimes more convenient instead of using an alert table just for that appender. The OL package provides a procedure which can be used by any appender in order to write into the LOGALE table (for details please see the OL package in-line documentation).
Appender Interface
Developers who want to write their own appenders must rely to a well defined and simple interface. In
order a package to be considered as a valid appender it must contain a log procedure
with the following signature:
procedure log(pi_header log_header_typ,
pi_message varchar2,
pi_params varchar2);
The pi_header parameter contains log header data, the pi_message is
the effective log message and the pi_params contains additional parameters passed
to the appender by the OL CORE.
OraLog Limitations
The following are some limitations of the OraLog framework:
- the maximum length of a log message is 32767 characters (standard CS) or 32K;
- the table appender can be used without any restrictions on a RAC system but PIPE or ALERT appenders might have some issues as a consequence of the corresponding DMBS_PIPE and DBMS_ALERT packages limitations on RAC systems;
- the DBGCND condition used for subscribing an appender is evaluated only if it is not NULL. This evaluation is made for every log message and for all subscribing appenders therefore it is important to avoid the usage of many appenders for systems in production because of performance issues which might occur. Likewise, having a bad designed appender will affect the OL CORE. If an appender is processing the logs very slow then the whole logging performance will be affected therefore the DBA will have to carefully plan which appenders will be used.
- it is not advisable to deactivate an appender by providing a FALSE condition to the appender subscription because it will be evaluated over and over again affecting the logging performance. The recommended way is to remove the subscription record from the subscription table or to set the APNACT field to 0.
- the maximum number of distinct placeholders within a log message is 10.