PRAGMA AUTONOMOUS_TRANSACTION In ORACLE

PRAGMA AUTONOMOUS_TRANSACTION:

The autonomous_transaction Pragma allows to mark sub transaction independent of main transaction.

Note:

1.    If an autonomous transaction attempts to access a resource held by the main transaction, a deadlock can occur.

2.    The database initialization parameter TRANSACTIONS specifies the maximum number of concurrent transactions. That number might be exceeded because an autonomous transaction runs concurrently with the main transaction

3.    If you try to exit an active autonomous transaction without committing or rolling back, the database raises an exception. If the exception goes unhandled, the transaction is rolled back.

Example1- Autonomous plsql block

declare
    v_emp_id int := 7369;   
    v_sal int := 100;
    pragma autonomous_transaction;
begin
    update emp_tst set sal = sal - v_sal where empno = v_emp_id;
    commit;
end;

Note: pragma autonomous_transaction can be written anywhere in the declaration section.

Example2- Autonomous triggers

A trigger must be autonomous to run TCL or DDL statements. To run DDL statements, the trigger must use native dynamic SQL.

Suppose we have defined a trigger on a table with commit (TCL) inside the trigger body then we will get the following error.

ORA-04092: cannot COMMIT in a trigger

create or replace trigger emp_tst_trg

before insert on emp_tst

for each row

begin

null;

commit;

end;

insert into emp_tst (select * from emp where rownum < 2)

ORA-04092: cannot COMMIT in a trigger

ORA-06512: at "EMP_TST_TRG", line 3

ORA-04088: error during execution of trigger 'EMP_TST_TRG'

To avoid this error we have to rewrite the trigger by using Pragma autonomous_transaction.

create or replace trigger emp_tst_trg

before insert on emp_tst

for each row

declare

    pragma autonomous_transaction;

begin

    null;

    commit;

end;

DDL statement test with in trigger body

Suppose we have a trigger on table emp_tst which have the drop table statement on the same table then we will get the following error

create or replace trigger emp_tst_trg

before insert on emp_tst

for each row

declare

    pragma autonomous_transaction;

begin

    execute immediate 'drop table emp_tst';

    commit;

end;

insert into emp_tst (select * from emp where rownum < 2)  à gives error as follows

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ORA-06512: at "EMP_TST_TRG", line 4

ORA-04088: error during execution of trigger 'EMP_TST_TRG'

Example3- Invoking autonomous functions from SQL

Create a log table

create table log_tab (text varchar2(100))

create a packaged function 

create package pragma_auto_test_pkg as

    function log_fun return varchar2;

end;

Create package function as autonomous transaction

create or replace package body pragma_auto_test_pkg as

    function log_fun return varchar2 as

        pragma autonomous_transaction;

        begin

            insert into log_tab values ('msg from pragma_auto_test_pkg.log_fun function');

            commit;

            return 'MSG From pragma_auto_test_pkg.log_fun function';

        end;

end;

Now, call the packaged function from select statement

select empno, ename, pragma_auto_test_pkg.log_fun

from emp

The above  call on emp table, inserts records into log_tab table

 select * from log_tab