From dgoulet@vicr.com Tue, 08 May 2001 13:55:51 -0700 From: dgoulet@vicr.com Date: Tue, 08 May 2001 13:55:51 -0700 Subject: Re:Pl/sql loop assistance Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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@ncc where sent = 1 ) loop update reg.docalert_responses@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" 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@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 Pl/sql loop assistance

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: dgoulet@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@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).