ORACLE – PLSQL- Autonomous Transactions

Oracle version: 10g and higher versions

Autonomous transactions are useful for implementing actions that need to be performed independently, regardless of whether the calling transaction commits or rolls back, such as transaction logging and retry counters.

Use the pragma AUTONOMOUS_TRANSACTION.

Example:.

SQL> CREATE TABLE test_autonomous (

colA VARCHAR2(100) );

Table created

SQL> CREATE OR REPLACE PROCEDURE Test_Auto IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO test_autonomous
VALUES('Autonomous Transaction');
COMMIT;
END Test_Auto;
/

Procedure created

SQL> SELECT *
FROM test_autonomous;

No records selected.

SQL> DECLARE
BEGIN
INSERT INTO test_autonomous
VALUES('Main Transaction');
-- call the autonomous transaction
Test_Auto();
-- rollback the main transaction
ROLLBACK;
END;
/

PL/SQL procedure successfully completed

SQL> SELECT *
FROM test_autonomous;

COLA
------------------------------------------------
Autonomous Transaction

According to the above example the autonomous transaction is not affected by the main transaction’s ROLLBACK.

When to use Autonomous Transactions?

• Logging mechanism

you need to log an error to your database log table. On the other hand, you need to roll back your core transaction because of the error. And you don't want to roll back over other log entries.

• Commits and rollbacks in your database triggers

If you define a trigger as an autonomous transaction, then you can commit and/or roll back in that code.

• Retry counter

Suppose that you want to let a user try to get access to a resource N times before an outright rejection; you also want to keep track of attempts between connections to the database. This persistence requires a COMMIT, but one that should remain independent of the transaction.

• Software usage meter

You want to keep track of how often a program is called during an application session. This information is not dependent on, and cannot affect, the transaction being processed in the application.