AmZone

Web Hosting by Netfirms | Free Domain Names by Netfirms

AmZone

A m Z o n e

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.

Press the Back button of you Browser to go to previous page
Home