Transaction Independence in Oracle8i

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS