Yes, it can work but you can't be sure it will work
100% of the time.
- dgoulet_at_vicr.com a écrit : > Stephane,
>
> What you say can be true, I've seen as much, but
> I've also seen cases where
> it works just fine. The key appears to be not
> messing with the same data record
> more than once in a loop. The other, and sometimes
> more important key, is to
> make sure others are not messing with the same pile
> of records in a different
> way at the same time. Now that can get REAL messy.
>
> Dick Goulet
>
> ____________________Reply
> Separator____________________
> Author: =?iso-8859-1?q?paquette=20stephane?=
> <stephane_paquette_at_yahoo.com>
> Date: 5/9/2001 6:56 AM
>
> 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.
> > > >
> > > >
>
=== 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: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_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_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 - 11:29:50 CDT
