In a nested procedure environment, I have often felt the need to isolatethe transactions of the child/called procedure from the parent/calling procedure’s transactions. Until Oracle8i, it used to be a cumbersome process. Oracle8i has introduced a new feature called “autonomous transactions,” which help isolate the changes made in the current transaction independent of the calling transaction context. Common usage of autonomous transactions would be in error logging mechanism, applying commits, and rollbacks in the database triggers, and also for building reusable application components or cartridges.

To make a PL/SQL block autonomous, simply include the following statement in the declaration section:

 PRAGMA AUTONOMOUS_TRANSACTION;

Anonymous PL/SQL blocks, database functions, stored procedures, packaged procedures, methods of SQL object type, and database triggers can be declared as autonomous transactions.

The code sample shown below explains the use of autonomous transaction for error logging. Let’s say procedure Proc1 invokes procedure ErrorLog:

 procedure Proc1(p_empid varchar2, p_empname)  is begin  insert into emp(empid,empname) values(p_empid, p_empname);exception   when others then    errorlog(SQLCODE,SQLERRM);    raise_application_error(-20001, 'Data could not be inserted into Emp table.');end;procedure ErrorLog(v_errcode varchar2, v_errmsg varchar2) is   PRAGMA AUTONOMOUS_TRANSACTION;begin  insert into errorlog_table(errorcode, errormsg) values(v_errcode,v_errmsg);  commit;end;

In this example, the error message is committed into errorlog_table even though the calling transaction is rolled back.