of course, the truly neat way to do it is to use the mod function. And dont forget the trailing commit, or you will not commit your last set of updates:


declare

cursor C1 is
select rowid
from FILE_STORAGE_VOLUME
where FILE_STORAGE_VOLUME_ID is NULL;

v_rowid VARCHAR2(30);
v_count NUMBER:=0;

begin

open C1;
loop
fetch C1 into v_rowid;
exit when C1%NOTFOUND;

update FILE_STORAGE_VOLUME
set FILE_STORAGE_VOLUME_ID =
ka_standard.get_next_sequence('FILE_STORAGE_VOLUME_SEQ')
where ROWID = v_rowid;

v_count := v_count + 1;
if mod(v_count,200000)=0 then
commit;
end if;

end loop;
close C1;
commit;
end;


-----Original Message-----
From: Mark Hooper
[mailto:oracledba-ezmlmshield-x42130566.[Email address protected]
Sent: Friday, February 25, 2005 15:08
To: LazyDBA Discussion
Subject: RE: Can i use commit in PL/SQL block


Yes you can use a commit in a PL/SQL block.
We do it in scripts that have to perform massive updates - typically I'll
put a counter in to commit after every X number of rows have been processed.
Some of the updates I end up doing will update millions upon millions of
rows and rather than grossly inflate the UNDO I just do the commit.

I just did one this morning actually with a quick and dirty script...

declare

cursor C1 is
select rowid
from FILE_STORAGE_VOLUME
where FILE_STORAGE_VOLUME_ID is NULL;

v_rowid VARCHAR2(30);
v_count NUMBER:=0;

begin

open C1;
loop
fetch C1 into v_rowid;
exit when C1%NOTFOUND;

update FILE_STORAGE_VOLUME
set FILE_STORAGE_VOLUME_ID =
ka_standard.get_next_sequence('FILE_STORAGE_VOLUME_SEQ')
where ROWID = v_rowid;

v_count := v_count + 1;
if v_count > 200000 then
v_count := 0;
commit;
end if;

end loop;
close C1;

end;
/


Hope this helps.
Mark


-----Original Message-----
From: Shantinath S Savalekar
[mailto:oracledba-ezmlmshield-x3043844.[Email address protected]
Sent: Thursday, February 24, 2005 11:55 AM
To: LazyDBA Discussion
Subject: Can i use commit in PL/SQL block


Friends,
Can i use commit in PL/SQL block...could you tell us the advantage and
disadvantages if i use.

If in case we want to use then what is the option....


Regards
Shantinath
ZenSar Technlogies


----------------------------------------------------------------------------
--
The contents of this e-mail are confidential to the ordinary user of the
e-mail address to which it was addressed and may also be privileged. If you
are not the addressee of this e-mail you should not copy, forward, disclose
or
otherwise use it or any part of it in any form whatsoever. If you have
received this e-mail in error please notify us by telephone or e-mail the
sender by replying to this message, and then delete the e-mail and other
copies of it from your computer system. Thank you.

We believe this email to be virus free but do not warrant that this is the
case and we will not accept liability for any losses arising from any virus
being transmitted unintentionally by us.

We reserve the right to monitor all E-mail communications through our
network



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html





--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page