PRAGMA AUTONOMOUS_TRANSACTION

PRAGMA's are simply directives (instructions) for the Oracle PL/SQL compiler. The pragmaAUTONOMOUS_TRANSACTION instructs the compiler to treat the pl/sql block following the pragma as autonomous (independent) from the calling transaction. This means that any changes made to the database in the autonomous transaction are independent of the main transaction and are either committed or rolled back without affecting the main transaction. 

Oracle pl/sql autonomous transactions must explicitly either roll back or commit any changes before exiting and can be:-

• stand alone procedures or functions
• procedures/functions defined in a package (but not nested)
• triggers
• or schema-level anonymous pl/sql blocks

Autonomous transactions are often used for logging errors in Oracle PL/SQL applications. By making the error logging procedure autonomous you ensure that the error message itself is logged in the error log table and is committed whilst the transaction that encountered the error is rolled back. Then when the application has fini9shed (or indeed whilst it's still running) error messages can be examined and corrective action potentially applied. 

Let's look at a brief example. 

First we declare an anonymous transaction

CREATE OR REPLACE PROCEDURE log_details
(msg IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN

    INSERT INTO log(msg_id         ,log_msg,time_of_msg)
    VALUES         (log_seq.NEXTVAL,msg    ,SYSDATE);

    COMMIT; -- must commit or rollback

END;

Next, we have another transaction that calls this procedure.

BEGIN

    DELETE employees;
    log_msg('Deleting all employees');
    ROLLBACK;
    log_msg('after rollback of delete employees');

END;

After this, the employees table would be unchanged, but there would be 2 (new) rows in the LOG table.

For the details of all the conditions for autonomous transactions see the Oracle PL/SQL Language Reference manual.

2 Autonomous Transaction in Oracle

An autonomous transaction is an independent transaction to the main or parent transaction. If an Autonomous transaction is started by another transaction it is not nested, but  independent of parent transaction.

Assume A is not completed because some unhandled exception occurred after B and B will be successful since it is an autonomous transaction.

Syntax

a) Autonomous transaction with package

create or replace package body pkg1
is
procedure add_log(error_text varchar2) is
PRAGMA autonomous_transaction;
begin
…………
…………
end add_log;
………….
…………..
end pkg1;

b) Autonomous transaction with standalone procedure/function

create or replace procedure add_log
is
PRAGMA autonomous_transaction;
begin
…………
…………
end add_log;

c) Autonomous transaction with triggers

create or replace trigger trg1
after insert on emp for each row
declare
PRAGMA autonomous_transaction;
……..
begin
………..
commit;
………..
end;

Usage

There are many situations you can use autonomous transactions but I would say error or event logging and auditing are the classic examples.

Below code is for basic error logging using autonomous transaction

SQL> desc log;
Name                                   Null?    Type
—————————————– ——– ————-
N                            NOT NULL NUMBER(38)
MYTEXT                                   VARCHAR2(100)
D                                              TIMESTAMP(6)

SQL> select * from log;

no rows selected

declare
v varchar2(1);
begin
add_log(‘before’);
v := ‘aa’;     — this will raise error
add_log(‘after’);
end;
/

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5

SQL> select * from log;

N  MYTEXT  D
—————————————————————————
1   before      12-JUN-12 04.59.05.490000 PM

Even though pl/sql got error Autonomous Transaction procedure logged the error.

Word of caution :-

In-correct usage lead into more trouble,

Examples  :-

a) Autonomous Transaction with delete or update command.

b) Autonomous Transaction inside a trigger

They must be used very cautiously and use it only if you know exactly how transaction works in Oracle.