Re: Pl/sql loop assistance

From: <Diana_Duncan_at_ttpartners.com>
Date: Wed, 09 May 2001 09:08:00 -0700
Message-ID: <F001.002FDDFE.20010509075136@fatcity.com>

Yeah, Stephane, that's actually one of the reasons I suggested not using a commit-within cursor loop thing. The suggestion I made wouldn't do that -- or am I missing something?

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

                                                                                       
                                  
                    paquette stephane                                                  
                                  
                    <stephane_paquette@        To:     Multiple recipients of list 
ORACLE-L <ORACLE-L_at_fatcity.com>       
                    yahoo.com>                 cc:                                     
                                  
                    Sent by:                   Fax to:                                 
                                  
                    root_at_fatcity.com           Subject:     Re: Pl/sql loop assistance 
                                  
                                                                                       
                                  
                                                                                       
                                  
                    05/09/2001 10:56 AM                                                
                                  
                    Please respond to                                                  
                                  
                    ORACLE-L

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: =?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).




--
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).

Received on Wed May 09 2001 - 11:08:00 CDT