Oracle Autonomous Transaction

In Oracle's database products, an autonomous transaction is an independent transaction that is initiated by another transaction. It must contain at least one Structured Query Language (SQL) statement. Autonomous transactions allow a single transaction to be subdivided into multiple commit/rollback transactions, each of which will be tracked for auditing purposes.

When an autonomous transaction is called, the original transaction (calling transaction) is temporarily suspended. The autonomous transaction must commit or roll back before it returns control to the calling transaction. Once changes have been made by an autonomous transaction, those changes are visible to other transactions in the database.

Autonomous transactions can be nested. That is, an autonomous transaction can operate as a calling transaction, initializing other autonomous transactions within itself. In theory, there is no limit to the possible number of nesting levels.

• This autonomous transaction can be specified at subprogram level.
• To make any subprogram to work in a different transaction, the keyword 'PRAGMA AUTONOMOUS_TRANSATION' should be given in the declarative section of that block.
• It will instruct that compiler to treat this as the separate transaction and saving/discarding inside this block will not reflect in the main transaction.
• Issuing COMMIT or ROLLBACK is mandatory before going out of this autonomous transaction to the main transaction because at any time only one transaction can be active.
• So once we made an autonomous transaction we need to save it and complete the transaction then only we can move back to the main transaction.

Syntax:

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
.
BEGIN

[COMMIT|ROLLBACK]
END;
/

In the above syntax, the block has been made as an autonomous transaction.

Example :

DECLARE
L_salary NUMBER;
  PROCEDURE nested_block
    IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        UPDATE emp
        SET salary=salary+15000
        WHERE emp_no=1002;
        COMMIT;
    END;
BEGIN
    SELECT salary INTO l_salary FROM emp WHERE emp_no=1001; 
    Dbms_output.put_line('Before: Salary of 1001 is'||l_salary); 
    SELECT salary INTO l_salary FROM emp WHERE emp_no=1002;
    Dbms output.put_line('Before: Salary of 1002 is'||1_salary):
    UPDATE emp
    SET salary=salary+5000
    WHERE emp_no=1001;
    Nested_block;
    ROLLBACK;
    SELECT salary INTO l_salary FROM emp WHERE emp_no=1001;
    Dbms_output.put_line('After: Salary of 1001 is'||l_salary);
    SELECT salary INTO l_salary FROM emp WHERE emp_no=1002;
    Dbms_output.put_line('After: Salary of 1002 is'||l_salary);
END:
/

Output :

Before:Salary of 1001 is 15000 
Before:Salary of 1002 is 10000 
After:Salary of 1001 is 15000 
After:Salary of 1002 is 25000