| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Pl/sql loop assistance
I'll have to disagree with not using commit within a loop.
If you identify what a transaction is within your code, and write it accordingly, using commit where appropriate, you will not get ORA-1002 or ORA-1555, at least not due to your own code.
I've written a number of routines to do just that, and helped developers straighten out their procedures so that they would quit getting ORA-1555.
Putting commit outside of the loop is simply not practical for a procedure that is modifying a large amount of data, and/or takes a long time to do it.
The code below that I originally commented on will not cause ORA-1555.
begin
loop
update reg.docalert_responses_at_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_at_ncc
where sent = 1
);
commit;
exit when sql%notfound;
end loop;
Jared
On Wednesday 09 May 2001 07:56, paquette stephane wrote:
> 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 <jkstill_at_cybcon.com> 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_at_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_at_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_at_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_at_ttpartners.com
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Hagedorn,
> > > >
> > > >
> > > > Linda" To:
> > > > Multiple recipients of list ORACLE-L
> > > > <ORACLE-L_at_fatcity.com>
> > > > <lindah_at_epocr cc:
> > > >
> > > >
> > > > ates.com> Fax to:
> > > >
> > > >
> > > > Sent by:
> >
> > Subject:
> > > > Pl/sql loop assistance
> > > >
> > > > root_at_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_at_ncp and
> > > > reg.docalert_emails_05040_at_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_at_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_at_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_at_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_at_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_at_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).Received on Wed May 09 2001 - 20:03:43 CDT
 |
 |