Oracle PL/SQL Best Practices

Name

SQL-03: Use autonomous transactions to isolate the effect of COMMITs and ROLLBACKs (Oracle8i).

Synopsis

A new feature in Oracle8i called autonomous transactions allows you to make and save (or roll back) changes within a single PL/SQL block—without affecting the outer or main transaction.

To make a PL/SQL block an autonomous transaction, simply include this statement in the declaration section of the block:

PRAGMA AUTONOMOUS_TRANSACTION;

You can use this statement in any procedure and function and in any non-nested anonymous block.

Example

CREATE OR REPLACE PROCEDURE log_error (
   code IN INTEGER, msg IN VARCHAR2)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO error_log 
      (errcode, errtext, created_on, created_by)
   VALUES 
      (code, msg, SYSDATE, USER);

   COMMIT;
EXCEPTION
   WHEN OTHERS THEN ROLLBACK;
END;

Benefits

With autonomous transactions, you can write and save messages in an database log without affecting the main transaction.

You can execute from SQL PL/SQL functions that change the database.

You can write PL/SQL components or cartridges that behave nicely in a distributed computing environment.

Resources

log.pkg and log.tst: A simple logging package that uses autonomous transactions, and a companion script you can use to test the functionality.