Very strange problem.

From: Scarab <yw_at_lucent.com>
Date: Wed, 28 Dec 2005 09:17:29 +0800
Message-ID: <dosp3d$cp8@netnews.net.lucent.com>

Hi,
Something strange happened to me:

declare
v_rowid varchar(18);
cursor icur is
select rowid from itest
where (idate < SYSDATE - 30);
begin

     open icur;
     loop
         fetch icur into v_rowid;
         exit when icur%NOTFOUND;
         delete from itest where rowid = v_rowid;
         if (mod(icur%ROWCOUNT,3000) = 0) then
            commit;
         end if;
     end loop;
     close icur;
     commit;

end;

I use the above sql to delete some data from table itest, but after execute the sql, I use
select * from itest where (idate < SYSDATE - 30); There are still some records that not deleted. What's wrong? After running the SQL again and again, all the records can be deleted. By the way, table itest is accessed very frequently, updating and inserting all the time.
Thanks. Received on Tue Dec 27 2005 - 19:17:29 CST