Re: Pl/sql loop assistance

From: <Diana_Duncan_at_ttpartners.com>
Date: Tue, 08 May 2001 13:52:10 -0700
Message-ID: <F001.002FD117.20010508134353@fatcity.com>

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). Received on Tue May 08 2001 - 15:52:10 CDT