Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Advanced
Apr 10, 2000

Transaction Independence in Oracle8i


In a nested procedure environment, I have often felt the need to isolate the 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.
Jayanta Sengupta
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date