<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"\@MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal">Hi All,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Is putting a commit or rollback in the processing of a for loop a good idea or not?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I’ve got several examples where it all works exactly as I would hope, and yet we’ve recently seen error ‘E_US11FD The cursor is not currently positioned on a row.’ on some specific cases.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">The reason the commits are in place is that we use the for loop to process some data on the basis of which we may do an update into some table. The possibility exists that the update triggers a constraint or rules based violation. If so
those violations will do a raise error and thus in the DEP all my statements get rolled back. Hence we want to commit after a successful update to prevent a subsequent failure from rolling it back.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Initial testing showed that the concept seemed sound…<o:p></o:p></p>
<p class="MsoNormal">Eg.<o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">create procedure good_loop<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">as declare<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> reltid integer4 not null not default;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> reltidx integer4 not null not default;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> relid char(256) not null not default;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> relowner char(32) not null not default;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> iloop integer4 not null not default;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> ndone integer4 not null not default;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> nfail integer4 not null not default;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> enum integer4 not null not default;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> msg varchar(256) not null not default;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">begin<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> iloop = 0;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> ndone = 0;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> nfail = 0;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> for select reltid, reltidx, relid, relowner<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> into :reltid, :reltidx, :relid, :relowner<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> from iirelation<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> order by 1, 2<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> do<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> iloop = iloop + 1;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> msg = '[' + varchar(:iloop) + '] for reltid/reltidx = ' + varchar(:reltid) + '/' + varchar(:reltidx);<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> message :msg;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> select iierrornumber into :enum;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> if (:enum != 0) then<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> nfail = nfail + 1;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> msg = 'Failed with enum = ' + varchar(:enum);<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> message :msg;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> rollback;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> else<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> ndone = ndone + 1;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> msg = 'Completed update of base';<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> message :msg;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> commit;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> endif;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> if (iloop > 10) then<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> -- 10 is sufficient to demonstrate the issue.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> endloop;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> endif;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> endfor;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> msg = 'good_loop(): Encountered ' + varchar(:iloop) + '. Updated ' + varchar(:ndone) +', Failed ' + varchar(:nfail);<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> message :msg;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">end;<o:p></o:p></span></p>
<p class="MsoNormal">Any ideas?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Marty<o:p></o:p></p>
</div>
</body>
</html>