Building an autonomous logging mechanism

A very common requirement in applications is keeping a log of errors that occur during transaction processing. The most convenient repository for this log is a database table; with a table, all the information is retained in the database, and you can use SQL to retrieve and analyze the log.

One problem with a database table log, however, is that entries in the log become a part of your transaction. If you perform a ROLLBACK (or if one is performed to you), you can easily erase your log. How frustrating! You can get fancy and use savepoints to preserve your log entries while cleaning up your transaction, but that approach is not only fancy, it is complicated. With autonomous transactions, however, logging becomes simpler, more manageable, and less error prone.

Suppose that I have a log table defined as follows:

/* File on web: log.pkg */CREATE TABLE logtab ( code INTEGER, text VARCHAR2(4000), created_on DATE, created_by VARCHAR2(100), changed_on DATE, changed_by VARCHAR2(100), machine VARCHAR2(100), program VARCHAR2(100) );

I can use it to store errors (SQLCODE and SQLERRM) that have occurred, or even for non-error-related logging. The machine and program columns record information available from the virtual V$SESSION table, as you will see.

So I have my table. Now, how should I write to my log? Here's what you should not do:

EXCEPTION WHEN OTHERS THEN v_code := SQLCODE; v_msg := SQLERRM; INSERT INTO logtab VALUES ( v_code, v_msg, SYSDATE, USER, SYSDATE, USER, NULL, NULL);END;

In other words, never expose your underlying logging mechanism by explicitly inserting into it your exception sections and other locations. Instead, you should build a layer of code around the table (this is known as encapsulation). There are two reasons to do this:

· If you ever change your table's structure, all those uses of the log table will not be disrupted.

· People will be able to use the log table in a much easier, more consistent manner.

So here is my very simple logging package. It consists of two procedures:

CREATE OR REPLACE PACKAGE logIS PROCEDURE putline ( code_in IN INTEGER, text_in IN VARCHAR2); PROCEDURE saveline ( code_in IN INTEGER, text_in IN VARCHAR2);END;

What is the difference between putline and saveline? The log.saveline procedure (as you will see in the package body) is an autonomous transaction routine, whereas log.putline simply performs the insert. Here is the package body:

/* File on web: log.pkg */CREATE OR REPLACE PACKAGE BODY logIS PROCEDURE putline ( code_in IN INTEGER, text_in IN VARCHAR2) IS BEGIN INSERT INTO logtab VALUES ( code_in, text_in, SYSDATE, USER, SYSDATE, USER ); END; PROCEDURE saveline ( code_in IN INTEGER, text_in IN VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN putline (code_in, text_in); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END;END;

Here are some comments on this implementation that you might find helpful:

· I obtain some useful information from V$SESSION when the package is initialized (the values will not change during my session, so I should query it only once) and incorporate that into the log.

· The putline procedure performs the straight insert. You would probably want to add some exception handling to this program if you applied this idea in your production application.

· The saveline procedure calls the putline procedure (I don't want any redundant code), but does so from within the context of an autonomous transaction.

With this package in place, my error handler shown earlier can be as simple as this:

EXCEPTION WHEN OTHERS THEN log.saveline (SQLCODE, SQLERRM);END;

No muss, no fuss. Developers don't have to concern themselves with the structure of the log table; they don't even have to know they are writing to a database table. And because I have used an autonomous transaction, they can rest assured that no matter what happens in their application, the log entry has been saved.