Preserve log table entries using autonomous transactions in Oracle

Developers can use autonomous transactions in Oracle to "step outside" the main transaction and do work that commits independently. A stored procedure for logging errors to a database table demonstrates this feature.

A database transaction is a unit of work that must either succeed completely or fail completely. In Oracle, a transaction starts with the first Data Management Language (DML) statement you execute, and ends when you issue a COMMIT statement to save the transaction or a ROLLBACK statement to throw it away.

The "all or nothing" nature of transactions can make it difficult to log error messages to a database table because the INSERT statement that is used to write the log entry will be undone when the transaction is rolled back.

Oracle provides a convenient way around this dilemma: the autonomous transaction. Autonomous transactions execute in their own context from the current transaction. They can be committed or rolled back separately without affecting the transaction in progress. This makes them ideal for writing to an error log table. Upon detecting an error in the transaction, you can insert a row into the error log table and commit it, and then roll back the main transaction without losing the insert.

Because they're separate from the main transaction, autonomous transactions cannot see the current state of rows that have been modified. It is as if they are in a separate session—until the main transaction is committed, they're not available to the autonomous one. The reverse is not true, however: The main transaction can see the results of an autonomous transaction that has already committed.

To create autonomous transactions, you must use the PL/SQL statement PRAGMA AUTONOMOUS_TRANSACTION at the top level of an anonymous block, or in the declaration section of a stored procedure, function, package, or trigger. SQL Server statements executed within such a block or procedure are autonomous.

Weekly Oracle tips in your inbox
TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more.
Automatically sign up today!

Triggers cannot contain COMMIT statements, unless they are marked with the PRAGMA AUTONOMOUS_TRANSACTION. However, only statements within the trigger will be committed, and not the main transaction.

Listing A shows CREATE TABLE and CREATE SEQUENCE statements for a simple but flexible error log table. Listing B is an autonomous stored procedure that updates the error log table. The procedure accepts up to three number and three text arguments, and then stores them in the table along with a timestamp and the user who called the procedure.

To test the procedure, UPDATE or DELETE some rows in a table; this begins the main transaction. Then execute the stored procedure, passing it your choice of data to be logged. Finally, roll back the main transaction, and SELECT the error log table. Your log entry will still be there.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

Editor's Picks

Free Newsletters, In your Inbox