Using PRAGMA AUTONOMOUS_TRANSACTION

Autonomous transaction is available from Oracle 8i onward.
The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. An autonomous transaction is an independent subprogram which called by the main transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independently from its parent transaction.

The autonomous transaction must be committed or roll backed before the autonomous transaction is ended and the parent transaction continues.

To define PL/SQL block as an autonomous transaction is quite easy. Just simply include the following statement in your declaration section:


PROCEDURE test
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Insert…
…
…
Commit;
END test;


Comparison of normal transaction with AUTONOMOUS TRANSACTION:

Normal Transaction example:


SQL:> declare
2 Procedure Insert_Test_Table_B
3 is
4 BEGIN
5 INSERT into Test_Table_B(x) values (1);
6 Commit;
7 END ;
8 BEGIN
9 INSERT INTO Test_Table_A(x) values (123);
10 Insert_Test_Table_B;
11 Rollback;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL :> Select * from Test_Table_A;
X
----------
123
SQL :> Select * from Test_Table_B;
X
----------
1
When the compiler execute until line 10, Insert_test_table_b subprogram was called. In Insert_test_table_b a COMMIT command was issued and all transactions were committed, therefore the ROLLBACK command at line 11 didn’t change any value.

AUTONOMOUS TRANSACTION:

SQL :> declare
2 Procedure Insert_Test_Table_B
3 is
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 INSERT into Test_Table_B(x) values (1);
7 Commit;
8 END ;
9 BEGIN
10 INSERT INTO Test_Table_A(x) values (123);
11 Insert_Test_Table_B;
12 Rollback;
13 END;
14 /
PL/SQL procedure successfully completed.
SQL :> Select * from Test_Table_A;
no rows selected
SQL :> Select * from Test_Table_B;
X
----------
1
In AUTONOMOUS TRANSACTION case, the COMMIT command in Insert_Test_Table_B only commit the transaction that was done in Insert_Test_Table_B itself. Therefore when a ROLLBACK command was issued at line 12, insert operation at Test_Table_A was undo.