2.2 Defining Autonomous Transactions

There isn't much involved in defining a PL/SQL block as an autonomous transaction. You simply include the following statement in your declaration section:

PRAGMA AUTONOMOUS_TRANSACTION;

The pragma instructs the PL/SQL compiler to establish a PL/SQL block as autonomous or independent. For the purposes of the autonomous transaction, a PL/SQL block can be any of the following:

• Top-level (but not nested) anonymous PL/SQL blocks

• Functions and procedures, defined either in a package or as standalone programs

• Methods (functions and procedures) of a SQL object type

• Database triggers

You can put the autonomous transaction pragma anywhere in the declaration section of your PL/SQL block. You would probably be best off, however, placing it before any data structure declarations. That way, anyone reading your code will immediately identify the program as an autonomous transaction.

This pragma is the only syntax change made to PL/SQL to support autonomous transactions. COMMIT, ROLLBACK, the DML statements -- all the rest is as it was before. However, these statements have a different scope of impact and visibility (discussed later in this chapter) when executed within an autonomous transaction, and you will have to include a COMMIT or ROLLBACK in your program.

Let's look at a simple example. Suppose you are responsible for building a database to keep track of war criminals for the International Court of Justice. You create a package called wcpkg to keep track of alleged war criminals. One of the programs in the package registers a new criminal. You always want that register program to save its changes, even if the calling program hasn't yet issued a COMMIT. These characters are, after all, fairly slippery and you don't want them to get away.

The package specification holds no surprises; the transaction type is not evident here:

CREATE PACKAGE wcpkg AS    ...    PROCEDURE register (       culprit IN VARCHAR2, event IN VARCHAR2); END wcpkg; /

The package body, however, contains that new and wonderful pragma:

CREATE PACKAGE BODY wcpkg AS    ...    PROCEDURE register (       culprit IN VARCHAR2, event IN VARCHAR2)    IS       PRAGMA AUTONOMOUS_TRANSACTION;    BEGIN       INSERT INTO war_criminal (name, activity)          VALUES (culprit, event);       COMMIT;    END; END wcpkg; /

Now when I call wcpkg.register, I am assured that my changes have been duly recorded:

BEGIN    wcpkg.register ('Kissinger', 'Secret Bombing of Cambodia');