From jkstill@cybcon.com Wed, 09 May 2001 06:26:00 -0700 From: Jared Still Date: Wed, 09 May 2001 06:26:00 -0700 Subject: Re: Pl/sql loop assistance Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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 > 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: Jared Still INET: jkstill@cybcon.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).