I thought I remembered a problem with %notfound.See Steven Feuerstein article in http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html. You might escape the problem because you check the %notfound AFTER processing the rows.
Adar Yechiel Rechovot, Israel Roger Xu wrote:
First of all, thanks for everyone's reply and sorry for my mistake not resetting counter back to zero after commit. We do have an index on SSN but I want to drop it to speed up the update. It looks to me that a better way is either CTAS or using bulk collect (below). Considering the standby database in place, I think I will go with bulk collect to reduce the redo transport. Thanks, Roger XuOn Fri, Nov 13, 2009 at 2:17 PM, Alisher Yuldashev <yuldashev@xxxxxxxxxxx <mailto:yuldashev@xxxxxxxxxxx>> wrote:Roger, I would do declare cursor c12 is select rowid rid from sales where ssn is not null; type v_typ is table of c12%rowtype; v_arr v_typ; begin open c12; loop fetch c12 bulk collect into v_arr limit 1000; forall i in 1..v_arr.count update sales set ssn=to_number(substr(to_char(ssn),-4)) where rowid=c12.rid(i); commit; exit when c12%notfound; end loop; close c12; end; Thanks,-- Alisher YuldashevSenior Oracle DBA The Pythian Group - Ottawa, Canada Web : http://www.pythian.com <http://www.pythian.com/>