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: =?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 - 07:36:20 CDT
