Re: Pl/sql loop assistance

[Diana_Duncan]

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@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@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: [EMAIL PROTECTED]


                                                                                       
                            
                    "Hagedorn,                                                         
                            
                    Linda"               To:     Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>       
                    <lindah@epocr        cc:                                           
                            
                    ates.com>            Fax to:                                       
                            
                    Sent by:             Subject:     Pl/sql loop assistance           
                            
                    root@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@ncp and
reg.docalert_emails_05040@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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).