Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Very strange problem.

Re: Very strange problem.

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Tue, 27 Dec 2005 21:26:49 -0500
Message-ID: <xPadnYMuePt3aizeRVn-vg@comcast.com>

"Scarab" <yw_at_lucent.com> wrote in message news:dosp3d$cp8_at_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.
>
>

SYSDATE includes time to the seconds, if idate does too, then you have a moving target -- every iteration addresses a different time slice

++ mcs Received on Tue Dec 27 2005 - 20:26:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US