Re: Pl/sql loop assistance

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