Autonomous Transactions

Autonomous Transactions

---

1. What is an Autonomous Transaction ?

---

An Autonomous Transaction is an independent transaction that is initiated by another transaction and executed without interfering with the parent transaction.
When an autonomous transaction is called, the originating transaction gets suspended. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.
Autonomous Transactions are used when we want to rollback some code while continuing to process an error logging procedure.

The term ‘autonomous transaction’

A Trigger or a Procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION.

An Autonomous Transaction has the following characteristics :

1. The Child Code runs independently of its parent
2. The Child Code can commit or rollback & parent resumes.
3. The Parent Code can continue without affecting child work.

There are many times when we might want to use an autonomous transaction to commit or rollback some changes to a table independently of a primary transaction’s final outcome.

Oracle PL/SQL autonomous transactions must explicitly either rollback 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

CREATE TABLE tab1(id NUMBER)
/

CREATE TABLE log(log_date DATE,log_comment VARCHAR2(500))
/

— Example to illustrate an Autonomous Transaction —
CREATE OR REPLACE TRIGGER tab1_trig
AFTER insert ON tab1
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log VALUES(SYSDATE,’Insert into TAB1′);
COMMIT; — Only allowed in Autonomous Triggers —
END;
/

INSERT INTO tab1 VALUES(100)
/

SELECT * FROM tab1
/

SELECT * FROM log
/

ROLLBACK
/

SELECT * FROM tab1
/

SELECT * FROM log
/

DELETE tab1
/

DELETE log
/

— Without the PRAGMA AUTONOMOUS_TRANSACTION the complete Transaction would have got restored (ROLLBACK) —

— Example to illustrate without Autonomous Transaction —
CREATE OR REPLACE TRIGGER tab1_trig
AFTER insert ON tab1
DECLARE
BEGIN
INSERT INTO log VALUES(SYSDATE,’Insert into TAB1′);
COMMIT;
END;
/

INSERT INTO tab1 VALUES(100)
/

SELECT * FROM tab1
/

SELECT * FROM log
/

ROLLBACK
/

SELECT * FROM tab1
/

SELECT * FROM log
/

DELETE tab1
/

DELETE log
/

DROP TABLE tab1 PURGE
/

DROP TABLE log PURGE
/

/*
Example of an Autonomous Transaction, we need to log errors into an Oracle Database Log Table. We need to Rollback the core transaction because of the resulting error,but we don’t want the error log to rollback.
*/

CREATE TABLE error_log
(log_code INTEGER,
log_mesg VARCHAR2(2000),
log_date DATE,
log_user VARCHAR2(20),
log_mach VARCHAR2(100),
log_prog VARCHAR2(100))
/

/*
We Create a Logging Procedure write_log, which inserts a row in the log table when a PL/SQL error occurs. We create a Procedure called write_log,with an error handler in the Exception part of the code —
*/

CREATE OR REPLACE PROCEDURE write_log
(log_code IN INTEGER,
log_mesg IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_log(log_code,log_mesg,log_date,log_user)
VALUES (log_code,log_mesg,SYSDATE,USER);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END write_log;
/

— We create another procedure which calls the write_log procedure in Exception Part —

CREATE OR REPLACE PROCEDURE add_emp
IS
BEGIN
INSERT INTO emp(ename)
VALUES(‘MILLER’);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
write_log(SQLCODE,SQLERRM);
ROLLBACK;
raise;
END;
/

Note :
Here the parent transaction (add_emp) will fail,while the child transaction (write_log) will proceed independently as an autonomous procedure.

EXEC add_emp;

SELECT * FROM error_log
/

As we can see,the error is logged in the Autonomous Transaction,but the main transaction is rolled back.

DROP TABLE error_log PURGE
/

— Another Example —

CREATE TABLE employee_master
AS SELECT * FROM emp
/

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

INSERT INTO error_log(log_mesg,log_date) 
VALUES(msg,SYSDATE);
COMMIT; -- must commit or rollback

END;
/

BEGIN
DELETE employee_master;
log_details(‘Deleting all employees’);
ROLLBACK;
log_details(‘after rollback of delete employees’);
END;
/

DROP TABLE employee_master PURGE
/

---

---