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:
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; /
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; /
BEGIN wcpkg.register ('Kissinger', 'Secret Bombing of Cambodia');
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.