Autonomous transaction in oracle

Autonomous transaction
==========================================
Structure:-
-----------

Declare
PRAGMA AUTONOMOUS_TRANSACTION;
.......
begin
…………….
……………
commit; ---or rollback;
End ;


Autonomous transactions perform an independent transaction, and return to the calling transaction without affecting it. The autonomous transaction has no link to the calling transaction, so only committed data can be shared by both transactions.
Oracle originally exposed Autonomous Transactions to developers as a mechanism for committing information on the side whilst not affecting the main event. They wanted to give us a way to record what happened in a job regardless of success or failure of the job itself, and without changing the transaction semantics of the main transaction.


The following types of PL/SQL blocks can be defined as autonomous transactions:
• Stored procedures and functions.
• Local procedures and functions defined in a PL/SQL declaration block.
• Packaged procedures and functions.
• Type methods.
• Top-level anonymous blocks.


Example


SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 5 11:19:42 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott@test107
Enter password:
Connected.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION dfr_name (passedinid IN NUMBER)
2 RETURN VARCHAR2
3 IS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 LAST VARCHAR2 (30);
6 BEGIN
7 SELECT emp.ename
8 INTO LAST
9 FROM emp
10 WHERE empno = passedinid AND ROWNUM = 1;
11
12 UPDATE emp
13 SET sal = sal + 100
14 WHERE empno = passedinid;
15
16 COMMIT;
17 RETURN LAST;
18 END;
19 /

Function created.

SQL> show error
No errors.
SQL>
SQL> select * from emp where empno='7369';

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 900
20


SQL> set linesize 1000
SQL> set pagesiz 100
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 900 20

SQL>
SQL> select dfr_name('7369') from dual;

DFR_NAME('7369')
------------------------------------------------------------------------------------------

SMITH

SQL>
SQL>
SQL> select * from emp where empno='7369';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 20

SQL>
SQL>
Autonomous transaction
=====================

Autonomous transaction feature is helpful for
1. A mutating table error in a trigger in which I am querying data from the table from which the trigger is fired.
2. I want to log an error so that when I rollback my business transaction, I do not lose my error log entry.
3. I need to keep an audit trail of every row that is retrieved by a set of queries in my application


A user-defined function cannot execute any DML (insert, update, delete, merge) if it is to be called inside an SQL statement, unless that function is marked as autonomous.
If you mark it as autonomous and also commit/rollback before leaving the function, then the SQL engine will let your function execute one or more DML statement(s).
The reason that this restriction is relaxed is that an autonomous transaction function executes in its own "transaction space" and does not share any locks with the session from which the SQL statement was executed.
This means that the function cannot see any of the uncommitted changes in that (or any other) session. It also means that any DML changes made and committed will not affect the results of the currently executing SQL statement.

If we want to use Autonomous Transactions in a known safe and efficient way, then we can keep just two simple rules in mind:
1) An Autonomous Transaction cannot see uncommitted changes from the parent transaction.
2) Autonomous Transactions should be used as “add on” capabilities which are essentially irrelevant to an otherwise wholly consistent piece of code.