Re:Pl/sql loop assistance

From: <dgoulet_at_vicr.com>
Date: Tue, 08 May 2001 13:55:51 -0700
Message-ID: <F001.002FD171.20010508134357@fatcity.com>

Linda,

Looks pretty simple, let's see:

set serveroutput on size 100000
declare
rows_processed number := 0;
begin
for response in (select docalert_response_id

                   from reg.docalert_emails_050401_at_ncc
                   where sent = 1 ) loop
      update reg.docalert_responses_at_ncp
      set reg.docalert_responses.campaign_response_handled = 1
      where docalert_response_id = response.docalert_response_id;
      rows_processed := rows_processed+sql%rowcount;
      if(rows_processed >= 100) then
          commit;
          rows_processed := 0;
      end if;

end loop;
commit;
exception when others then dbms_output.put_line(sqlerrm);

                           rollback;

end;
/

That should do it.

Dick Goulet

____________________Reply Separator____________________
Author: "Hagedorn; Linda" <lindah_at_epocrates.com>
Date:       5/8/2001 10:47 AM

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2653.12">
<TITLE>Pl/sql loop assistance </TITLE>
</HEAD>
<BODY>

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     
  
  


</BODY>
</HTML>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.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:55:51 CDT