PRAGMA AUTONOMOUS_TRANSACTION in PLSQL

    An autonomous transaction is an independent transaction started by another transaction i.e. the main transaction. Autonomous transactions do SQL operations and commit or rollback without commit or rolling back the main transaction.

The following types of PL/SQL blocks can be defined as autonomous transactions:
1) Stored procedures and functions.
2) Local procedures and functions defined in a PL/SQL declaration block.
3) Packaged procedures and functions.
4) Triggers
5) schema-level anonymous pl/sql blocks

Advantages of Autonomous transaction:
1) After started the autonomous transaction is fully independent.
2) It shares no locks or resources with main transaction.
3) It helps to build modular, re-usable components.

Lets look into in detail with an example
1) Create a table
CREATE TABLE test_update (empno NUMBER NOT NULL, ename VARCHAR2(30));

2) Insert a value to the table test_update
INSERT INTO test_update values (10,'RG');

3) Create another table to insert values in child procedure call
CREATE TABLE test_tab ( id NUMBER(10), msg VARCHAR2(4000) );

4)Create a main procedure
CREATE OR REPLACE PROCEDURE updateemp(i_empno number,NAME VARCHAR2) IS BEGIN UPDATE test_update SET ENAME='RG HEGDE' WHERE empno=10; --calling child procedure which is declared as PRGMA ----AUTONOMOUS_TRANSACTION and which has commit statement. auto_test (message => 'Calling Autonomous transaction test procedure'); --Rolling back the update statement in Main procedure ROLLBACK; exception when others then auto_test (message =>SQLERRM); ROLLBACK; END;

5)Create child procedure which has commit statement and declared as PRAGMA AUTONOMOUS_TRANSACTION
CREATE OR REPLACE PROCEDURE auto_test (message IN VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO test_tab(id ,msg) VALUES (12,message ); COMMIT; -- must commit or rollback END;

6)Check the values in the table test_update
SELECT * FROM test_update;

7)Call the Main procedure to update test_update , name from RG to RG HEGDE

BEGIN updateemp(10,'RG HEGDE'); END;

8) Check the table test_tab
select * from test_tab
The value will be inserted from child procedure and it is commited

9)Check the table test_update.
select * from test_update;
The ename column will not be updated. It shows that the child procedure executed independently.

Verification:
1)Re-Create the child procedure without PRAGMA AUTONOMOUS_TRANSACTION.
CREATE OR REPLACE PROCEDURE auto_test (message IN VARCHAR2) IS --PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO xxjg.test_tab(id ,msg) VALUES (12,message ); COMMIT; -- must commit or rollback END;

2) Call the main procedure
BEGIN updateemp(10,'RG HEGDE'); END;

3) Now check the table test_update
SELECT * FROM test_update;
The ename column will be changed from RG to RG HEGDE