Re: Very strange problem.

On Wed, 28 Dec 2005 15:38:49 +0800, "Scarab" <yw_at_lucent.com> wrote:

>OK, Thanks for your wonderful reply, that's the problem.
>And I have some other puzzles:
>Can I use "for update" in the declaration of a cursor?
>cursor icur is
> select rowid from itest
> where (idate < SYSDATE - 30) for update;
>
>If it can:
>1. it will only lock the records that fit the criteria and have
>nothing to do with the other records, is it right?
>2. When I commit records after delete 3000 rows,
> if (mod(icur%ROWCOUNT,3000) = 0) then
> commit;
> The cursor is no longer usable because the commit operation, how to
>workaroud it?
>
>Thank you very much.

1 Please stop top-posting.
2 for update is allowed in a cursor declaration 3 only affected records are locked
4 you don't need to commit in a loop. In Oracle 9i, just set your undo_retention_time appropiately. If this is a disk space issue, buy more disks.

--
Sybrand Bakker, Senior Oracle DBA