ÿþ<HTML><!-- #BeginTemplate "/Templates/summit.dwt" --><!-- DW6 --> <HEAD> <!-- #BeginEditable "doctitle" --> <title>Summit Software Design: Articles: Autonomous Transactions</title> <!-- #EndEditable --> <META http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <META name="keywords" content="Oracle consulting, PL/SQL, Oracle, Developer2000, PL/SQL cartridges, Oracle DBA, DBA services, Oracle Training, Oracle tuning, Oracle upgrades, invoices on the web, web invoices, consulting services, oracle services, view invoices on the web, tuning, oracle tuning, software consulting, oracle consultants, oracle tampa, oracle florida, tampa, web development, website design, tampa computer consultants, computer consultants tampa, VB, Khameleon custom work, Khameleon Software"> <META name="description" content="Application development, DBA Services, Web front-ends for Oracle based applications, and Web site design."> <meta http-equiv="content-type" content="text/html;charset=ISO-8859-1"> <style> A {color:000066;} A:Hover {color:990000;} </style> <SCRIPT LANGUAGE=JAVASCRIPT> <!-- var dayName = new Array ("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") var monName = new Array ("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December") var now = new Date //--> </SCRIPT> <script language="JavaScript"> <!-- <!-- function MM_reloadPage(init) { //reloads the window if Nav4 resized if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) { document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }} else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload(); } MM_reloadPage(true); // --> //--> </script> <script language="JavaScript"> <!-- function MM_swapImgRestore() { //v3.0 var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc; } function MM_preloadImages() { //v3.0 var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array(); var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++) if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}} } function MM_findObj(n, d) { //v4.01 var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n]; for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document); if(!x && d.getElementById) x=d.getElementById(n); return x; } function MM_swapImage() { //v3.0 var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3) if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];} } //--> </script> </HEAD> <BODY bgcolor="#000066" text="#000000" onLoad="MM_preloadImages('../images/main/dot_products.gif','../images/main/new_design_r2_c1_f2.gif','../images/main/new_design_r3_c1_f2.gif','../images/main/new_design_r4_c1_f2.gif','../images/main/new_design_r5_c1_f2.gif','../images/main/new_design_r6_c1_f2.gif','../images/main/new_design_r7_c1_f2.gif','../images/main/new_design_r8_c1_f2.gif')"> <div align="center"><A name="top"></A> <table width="760" border="0" cellspacing="0" cellpadding="4"> <tr> <td width="100%"> <table width="100%" border="0" cellspacing="0" cellpadding="1" bgcolor="#FFCC33"> <tr> <td width="100%"> <table width="100%" border="0" cellspacing="0" cellpadding="0" height="100%" bgcolor="#000066"> <tr> <td width="20" height="20"><img src="../images/main/spacer20x20.gif" width="20" height="20"></td> <td width="100%">&nbsp;</td> <td width="20" height="20"><img src="../images/main/spacer20x20.gif" width="20" height="20"></td> </tr> <tr> <td>&nbsp;</td> <td align="left" valign="top" bgcolor="#FFFFFF"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr align="left" valign="top"> <td width="322" height="52"><a href="../index.html"><img src="../images/main/new_design_r1_c1.gif" width="322" height="52" border="0"></a></td> <td width="40" height="52"><img src="../images/main/new_design_r1_c3.gif" width="40" height="52" name="dot"></td> <td valign="top"><img src="../images/main/new_design_r1_c4.gif" width="232" height="52"></td> </tr> <tr align="left" valign="top"> <td colspan="3" height="20"><img src="../images/main/spacer20x20.gif" width="20" height="20"></td> </tr> </table> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="120" align="left" valign="top"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td><a href="../index.html" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Home','','../images/main/new_design_r8_c1_f2.gif',1)"><img src="../images/main/new_design_r8_c1.gif" alt="Home" name="Home" width="120" height="25" border="0"></a></td> </tr> <tr> <td><a href="../summitdevelopment.html" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Services','','../images/main/new_design_r2_c1_f2.gif',1)"><img src="../images/main/new_design_r2_c1.gif" alt="Services" name="Services" width="120" height="25" border="0"></a></td> </tr> <tr> <td><a href="../articles.html" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Articles','','../images/main/new_design_r4_c1_f2.gif',1)"><img src="../images/main/new_design_r4_c1.gif" alt="Articles" name="Articles" width="120" height="25" border="0"></a></td> </tr> <tr> <td><a href="../aboutus.html" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('About','','../images/main/new_design_r5_c1_f2.gif',1)"><img src="../images/main/new_design_r5_c1.gif" alt="About Us" name="About" width="120" height="25" border="0"></a></td> </tr> <tr> <td><a href="../jobs.html" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Jobs','','../images/main/new_design_r6_c1_f2.gif',1)"><img src="../images/main/new_design_r6_c1.gif" alt="Jobs" name="Jobs" width="120" height="25" border="0"></a></td> </tr> <tr> <td><a href="../contact.html" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Contact','','../images/main/new_design_r7_c1_f2.gif',1)"><img src="../images/main/new_design_r7_c1.gif" alt="Contact Us" name="Contact" width="120" height="25" border="0"></a></td> </tr> </table> <p align="center">&nbsp;</p> <!-- #BeginEditable "sidebar" --> <P>&nbsp;</P> <P align="center">&nbsp;</P> <P>&nbsp;</P> <!-- #EndEditable --></td> <td align="left" valign="top"> <table width="100%" border="0" cellspacing="0" cellpadding="10" height="100%"> <tr valign="bottom"> <td align="left"><!-- #BeginEditable "title" --> <p><a href="../index.html"><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><b>Home</b></font></a> <b><font size="1" face="Verdana, Arial, Helvetica, sans-serif">&gt; <a href="../articles.html">Articles</a> &gt;</font></b></p> <p><font face="Arial, Helvetica, sans-serif" size="2" color="#000066"><b><b><font size="3">Autonomous Transactions</font></b></b></font></p> <!-- #EndEditable --></td> <td align="right" valign="top" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1"> <SCRIPT language=JAVASCRIPT> document.write(dayName[now.getDay()] + ", " + monName[now.getMonth()] + " " + now.getDate() + ", " + now.getFullYear() ) </SCRIPT> </font></td> </tr> <tr> <td valign="top" colspan="2"><!-- #BeginEditable "content" --> <p><font face="Arial, Helvetica, sans-serif" size="2"><i>By Arabinda Banerjee</i><br> </font></p> <font face="Arial, Helvetica, sans-serif" size="2"> <p><b>What is an Autonomous Transaction?</b> <p>Autonomous Transaction is a new feature in ORACLE. It allows setting up independent transactions that can be called from within other transactions. It lets you suspend the main transaction (without committing or rolling back), perform some DML operations, commit or roll back those operations (without any effect on the main transaction), and then return to the main transaction. <p>Being independent of the main transaction (almost like a separate session), an autonomous transaction does not see the uncommitted changes from the main transaction. It also does not share locks with the main transaction. As a result, it can get into a deadlock with its parent &#133; something the application developer should watch out for. <p>As expected, changes committed by an autonomous transaction are visible to other sessions/transactions immediately, regardless of whether the main transaction is committed or not. These changes also become visible to the main transaction when it resumes, provided its isolation level is set to <font face="Courier New, Courier, mono">READ COMMITTED</font> (which is the default). <p><b>Defining Autonomous Transactions</b> <p>A transaction can be marked as autonomous by putting that transaction in a separate stored procedure/function, or a packaged procedure/function, or a local procedure inside a stored procedure, or a separate database trigger. Anonymous PL/SQL blocks and methods of SQL object type can also be defined as autonomous transactions. <p>Any of these routines can be marked as autonomous simply by using the following syntax anywhere in the declarative section of the routine (putting it at the top is recommended for better readability): </font> <pre><FONT face="Arial, Helvetica, sans-serif" size="2"> </FONT>PRAGMA AUTONOMOUS_TRANSACTION;<FONT face="Arial, Helvetica, sans-serif" size="2"> </FONT></pre> <font face="Arial, Helvetica, sans-serif" size="2"> <p>Here is an example of defining a stored procedure as autonomous:</p> </font> <pre>CREATE PROCEDURE process_ord_line_shipment (p_order_no number, p_line_no number) AS PRAGMA AUTONOMOUS_TRANSACTION; l_char_1 varchar2(100); BEGIN ... END;<FONT face="Arial, Helvetica, sans-serif" size="2"> </FONT></pre> <font face="Arial, Helvetica, sans-serif" size="2"> <p>There are two restrictions on the use of this PRAGMA: it cannot be used to mark ALL members of a package as autonomous. Simple workaround: mark each member (proc/function) as autonomous individually. Also, a nested PL/SQL block cannot be marked as autonomous. Again, the workaround is not too bad: just make it a local procedure (or a separate stored procedure).</p> <p>The called routine should have its own commit (or rollback). This is true even for a trigger (which otherwise cannot have a commit or rollback statement). An attempt to exit without committing or rolling back the changes results in an exception condition (and the pending changes are rolled back as a result).</p> <p><b>Advantages of Autonomous Transactions</b> </p> <p>Autonomous transactions are likely to be quickly embraced by PL/SQL developers. They would find their use in many situations where some DML's needs to be saved regardless of whether or main transaction commits or rolls back. Here are a few examples:</p> <ol> <li><i>Transaction logging</i>: A transaction needs to be logged for audit purposes even if it fails and does not update any other data. An example of this is currently available under the sample PL/SQL code section of the Technet website (<a href="http://technet.oracle.com" target="_blank">technet.oracle.com</a>).</li> <li><i>Debugging (logging messages in an error-message table).</i> The procedure to insert a message into a debug table should be set up as an autonomous transaction so the messages are saved even if the main transaction fails or rolls back.</li> <li><i>Incrementing retry counters.</i> It is very similar to the transaction-logging situation.</li> </ol> <p>Apart from these technical needs, there is a significant advantage of using autonomous transactions from the point of view of programming style and structure. Since autonomous transactions can be committed or rolled back independent of the main transaction, it will facilitate development of more modular programs.</p> <p>Here is an example of debugging application (mentioned above) that demonstrates this very well:</p> </font> <pre> Procedure debug_write(p_err_msg in varchar2, p_proc in varchar2, & ) is PRAGMA AUTONOMOUS_TRANSACTION; BEGIN Begin Insert into debug messages (err_msg, proc_name, & ) Values(p_err_msg, p_proc, & ); Commit; Exception & end; END;<FONT face="Arial, Helvetica, sans-serif" size="2"> </FONT></pre> <font face="Arial, Helvetica, sans-serif" size="2"> <p>Once set up this way, it is a modular piece of code that can be called from any procedure/function/trigger without compromising the integrity of the calling transaction.</p> <p>This is a great benefit and will surely make PL/SQL programming more fun.</p> </font> <!-- #EndEditable --></td> </tr> </table> </td> </tr> <tr> <td width="120">&nbsp;</td> <td align="center" valign="top"> <hr align="center" size="1" width="95%"> <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="1"><a href="../index.html">HOME</a> &#149; <a href="../summitdevelopment.html">SERVICES</a> &#149; <a href="../articles.html">ARTICLES</a> &#149; <a href="../aboutus.html">ABOUT US</a> &#149; <a href="../jobs.html">JOBS</a> &#149; <a href="../contact.html">CONTACT US</a></font><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#999999"><br> &nbsp; </font></div> </td> </tr> </table> </td> <td>&nbsp;</td> </tr> <tr> <td width="20" height="20"><img src="../images/main/spacer20x20.gif" width="20" height="20"></td> <td>&nbsp;</td> <td width="20" height="20"><img src="../images/main/spacer20x20.gif" width="20" height="20"></td> </tr> <tr> <td height="1"><img height="1" width="20" src="/images/spacer.gif"></td> <td></td> <td><img height="1" width="20" src="/images/spacer.gif"></td> </tr> </table> </td> </tr> </table> </td> </tr> </table> <font face="arial" color="#999999" size="1"><font face="Verdana, Arial, Helvetica, sans-serif" color="#eaeaea">Copyright&copy; 2000-<script language=JAVASCRIPT> <!-- var right_now=new Date(); var right_year=right_now.getYear(); if (right_year < 2000) right_year = right_year + 1900; document.write( right_year ); // --></script> Summit Software Design, Inc. and its licensors. All Rights Reserved. <p> <!--WEBBOT bot="HTMLMarkup" startspan ALT="Site Meter" --> <script type="text/javascript" language="JavaScript">var site="sm1ssd01"</script> <script type="text/javascript" language="JavaScript1.2" src="http://sm1.sitemeter.com/js/counter.js?site=sm1ssd01"> </script> <noscript> <a href="http://sm1.sitemeter.com/stats.asp?site=sm1ssd01" target="_top"> <img src="http://sm1.sitemeter.com/meter.asp?site=sm1ssd01" alt="Site Meter" border=0></a> </noscript> <!-- Copyright (c)2002 Site Meter --> <!--WEBBOT bot="HTMLMarkup" Endspan --> </font></font></div> <script language="JavaScript1.2"> /* Disable right click script II (on images)- By Dynamicdrive.com For full source, Terms of service, and 100s DTHML scripts Visit http://www.dynamicdrive.com */ var clickmessage="Copyright 2000-2001 Summit Software Design, Inc. and its licensors. All rights reserved." function disableclick(e) { if (document.all) { if (event.button==2||event.button==3) { if (event.srcElement.tagName=="IMG"){ alert(clickmessage); return false; } } } else if (document.layers) { if (e.which == 3) { alert(clickmessage); return false; } } else if (document.getElementById){ if (e.which==3&&e.target.tagName=="IMG"){ alert(clickmessage) return false } } } function associateimages(){ for(i=0;i<document.images.length;i++) document.images[i].onmousedown=disableclick; } if (document.all) document.onmousedown=disableclick else if (document.getElementById) document.onmouseup=disableclick else if (document.layers) associateimages() </script> </BODY> <!-- #EndTemplate --></HTML>