Autonomous Transaction in Oracle

An autonomous transaction is an independent transaction to the main or parent transaction. If an autonomous transaction is started by another transaction it is not nested, but  independent of parent transaction.

Assume A is not completed because some unhandled exception occurred after B and B will be successful since it is an autonomous transaction.

When an autonomous transaction is called, the originating transaction gets suspended. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.

A trigger or procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION. The pragma AUTONOMOUS_TRANSACTION instructs the compiler to treat the pl/sql block following the pragma as autonomous (independent) from the calling transaction. This means that any changes made to the database in the autonomous transaction are independent of the main transaction and are either committed or rolled back without affecting the main transaction.

There are many situations you can use autonomous transactions such as error or event logging and auditing are the classic examples. By making the error logging procedure autonomous you ensure that the error message itself is logged in the error log table and is committed whilst the transaction that encountered the error is rolled back. Then when the application has finished (or indeed whilst it's still running) error messages can be examined and corrective action potentially applied.
 

Autonomous transaction with package

create or replace package body pkg1
is

  procedure add_log(error_text varchar2) is
  PRAGMA autonomous_transaction;
  begin
     …………
     …………
     commit;

  end add_log;

  …………
  …………
end pkg1;

Autonomous transaction with triggers

create or replace trigger trg1
after insert on emp for each row
declare
PRAGMA autonomous_transaction;
……..
begin
   ………..
   commit;
end;