Use autonomous transactions to record to logs

An autonomous transaction is a transaction that can be started within the context of another transaction (parent transaction), but is independent of it. The autonomous transaction can be committed or rolled back regardless of the state of the parent transaction.

Sometimes it becomes necessary to update a table irrespective of the current status of transaction. An example is writing to the logs table. Consider the following scenario in which statement2 has to be executed irrespective of rollback or commit:

Statement1; 
Statement2; 
Statement3; 
Condition     if true 
                    commit; 
               else 
                   rollback; 

Here is how to do it. (This tip was tested on Oracle 8i and I am sure that it will work in 9i also.)

1. Define the PL/SQL block as an autonomous transaction by using- PRAGMA AUTONOMOUS_TRANSACTION
2. Execute SQL
3. Commit or rollback
4. Call / use this block in another transaction

Implementation:

A procedure can be written which may be called from other procedures. It takes a varchar2 data as argument. Procedure definition is as follows:

CREATE OR REPLACE PROCEDURE Insertlogs(logmsg IN VARCHAR2) IS
PRAGMA autonomous_transaction;
tmpVar NUMBER;

BEGIN

tmpVar := 0;

INSERT INTO LOGS123(LOGMESSAGE) VALUES(logmsg);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
/

Insertlogs('this is autonomous logs');

Reader Feedback

Bruce S. writes: The tip is a good start. I've been using this method ever since 8i came out. But I think the concept needs to be extended. I suggest that a timestamp be used at the very least. I use sysdate for 8i and systimestamp for 9i. Other suggestions are to add a module_name and severity_level. This would make the log much more useful and easy to search by time, application, or severity level.

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.