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" <[EMAIL PROTECTED]>
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
<!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>
<P><FONT SIZE=2 FACE="Arial">Hello, </FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">I'm having difficulty coding this loop and am
hoping someone can see how this can be done. </FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">I have two tables, one on each instance
reg.docalert_responses@ncp and reg.docalert_emails_05040@ncc </FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">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. </FONT></P>
<P><FONT SIZE=2 FACE="Arial">I know I can set autocommit, but I'd really like to
see the elegant loop logic. The DBLinks are in place. </FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">Any assistance is appreciated. </FONT>
<BR>
<BR><FONT SIZE=2 FACE="Arial">Thanks, Linda </FONT>
<BR><FONT SIZE=2 FACE="Arial"> </FONT>
<BR><FONT SIZE=2 FACE="Arial"> </FONT>
</P>
</BODY>
</HTML>
--
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).
