Autonomous transactions
Is it possible to commit changes in a called routine, without committing or rolling back
changes in the calling routine?
Routine A does update on some records in a table. It then calls routine B that inserts
records in an audit table and commits the data. When the control is returned to routine A,
based on some condition, the update done earlier is rolled back. The requirement is that
the changes done by routine B are not rolled back.
At times, you may want to commit or rollback some changes to a table independently of a primary transaction's final outcome. Setting a routine as autonomous tells oracle to run the routine independent of the called routines status, the moment it is initiated. In other words, once an
autonomous routine is called, the transaction in it committed, irrespective of whether the
calling routine transaction was committed or not. Check the example below for better understanding.
eg:
select * from amemp
where ename in ('KING', 'BLAKE');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
_____ __________ _________ __________ _________ __________ __________ __________
7839 KING PRESIDENT 17-NOV-81 500 10
7698 BLAKE MANAGER 7839 01-MAY-81 28.5 30
create or replace procedure updsal is
pragma autonomous_transaction;
begin
update amemp
set sal = 700
where ename = 'BLAKE';
commit;
end;
/
begin
update amemp
set sal = 1000
where ename = 'KING';
updsal();
rollback;
end;
/
select * from amemp
where ename in ('KING', 'BLAKE');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
_____ __________ _________ __________ _________ __________ __________ __________
7839 KING PRESIDENT 17-NOV-81 500 10
7698 BLAKE MANAGER 7839 01-MAY-81 700 30
Note that the commit in the called procedure has not committed all the transactions. It will only commit the transaction taking place in the procedure. A trigger can also be made autonomous in
nature.However there are some issues when a trigger is made autonomous. Check article
"Invoking the Deadlock ORA-000060" in Oracle articles page for more information.
Best viewed in medium text size. Please refresh this page (F5) to view the latest information. This page was created on 16-dec-2001 and last updated on 16-dec-2001.
please forward all queries to amar_padhi@fastmail.fm