From stephane_paquette@yahoo.com Wed, 09 May 2001 08:37:22 -0700 From: =?iso-8859-1?q?paquette=20stephane?= Date: Wed, 09 May 2001 08:37:22 -0700 Subject: Re: Pl/sql loop assistance Message-ID: MIME-Version: 1.0 Content-Type: text/plain Jared, They are many causes for the famous "ORA-1555 Snapshot too old", one of them is fetch across commit. It is when you're commiting and fetching the same data. it is not accept in ANSI SQl but it is by Oracle. In numerous place, developpers have complained that I sized the rbs too small because of the ORA-1555. Suddenly my rbs were ok after moving the commit outside the loop . --- Jared Still a écrit : > > Stephane, > > This doesn't look like it will cause ORA-1002, at > least > I don't see it. > > What's the relationship between ORA-1002 and > ORA-1555? > > I got up rather early with a headache this morning, > so maybe I'm > just not thinking clearly yet. :) > > Jared > > On Wednesday 09 May 2001 05:40, paquette stephane > wrote: > > My observation is not on the elegancy of the code > but > > why commit at 100 rows ? Are you updating 10 000 > 000 > > rows ? > > I've seen a lot of ORA-1555 because of fetch > across > > commit. > > > > > > --- Diana_Duncan@ttpartners.com a écrit : > > > > > > Linda, > > > > > > Might I suggest avoiding the "elegant" looping > and > > > try some "inelegant" > > > looping? It should be faster, although I can't > make > > > any promises. > > > > > > Warning, untested, and you can probably do > better > > > than an in() -- but it > > > should give you the gist... > > > > > > begin > > > loop > > > update reg.docalert_responses@ncp > > > set campaign_response_handled = 1 > > > where campaign_response_handled != 1 > > > and rownum <= 100 > > > and docalert_response_id in (select > > > docalert_response_id > > > from docalert_emails_050401@ncc > > > where sent = 1); > > > commit; > > > exit when sql%notfound; > > > end loop; > > > end; > > > / > > > > > > Diana Duncan > > > TITAN Technology Partners > > > One Copley Parkway, Ste 540 > > > Morrisville, NC 27560 > > > VM: 919.466.7337 x 316 > > > F: 919.466.7427 > > > E: Diana_Duncan@ttpartners.com > > > > > > > > > > > > > > > > > > "Hagedorn, > > > > > > > > > Linda" To: > > > Multiple recipients of list ORACLE-L > > > > > > > > > > > > > > ates.com> Fax to: > > > > > > > > > Sent by: > Subject: > > > Pl/sql loop assistance > > > > > > root@fatcity. > > > > > > > > > com > > > > > > > > > > > > > > > > > > > > > > > > > > > 05/08/2001 > > > > > > > > > 02:47 PM > > > > > > > > > Please > > > > > > > > > respond to > > > > > > > > > ORACLE-L > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Hello, > > > > > > > > > I'm having difficulty coding this loop and am > hoping > > > someone can see how > > > this can be done. > > > > > > > > > I have two tables, one on each instance > > > reg.docalert_responses@ncp and > > > reg.docalert_emails_05040@ncc > > > > > > > > > The requirement is to set > > > > ncp.reg.docalert_responses.campaign_response_handled > > > = 1 for all > > > docalert_response_id's that exist in > > > ncc.reg.docalert_emails_050401 where > > > sent=1. Update 100 at a time and commit. The > join > > > column, > > > docalert_response_id appears in both tables. > > > > > > > > > I know I can set autocommit, but I'd really like > to > > > see the elegant loop > > > logic. The DBLinks are in place. > > > > > > > > > Any assistance is appreciated. > > > > > > Thanks, Linda > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: > > > http://www.orafaq.com > > > -- > > > Author: > > > INET: Diana_Duncan@ttpartners.com > > > > > > Fat City Network Services -- (858) 538-5051 > FAX: > > > (858) 538-5051 > > > San Diego, California -- Public Internet > > > access / Mailing Lists > > > > > -------------------------------------------------------------------- > > > > > To REMOVE yourself from this mailing list, send > an > > > E-Mail message > > > to: ListGuru@fatcity.com (note EXACT spelling of > > > 'ListGuru') and in > > > the message BODY, include a line containing: > UNSUB > > > ORACLE-L > > > (or the name of mailing list you want to be > removed > > > from). You may > > > also send the HELP command for other information > > > (like subscribing). > > > > ===== > > Stéphane Paquette > === message truncated === ===== Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant stephane_paquette@yahoo.com ___________________________________________________________ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: stephane_paquette@yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).