Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Very strange problem.
Thanks for your reply, here comes my sp:
create or replace procedure clitest
as
v_rowid varchar(18);
msg varchar2(256);
cursor icur is
select rowid from itest
where (idate < SYSDATE - 30) for update nowait;
begin
open icur; loop fetch icur into v_rowid; exit when icur%NOTFOUND; delete from itest where rowid = v_rowid ; if (mod(icur%ROWCOUNT,1000) = 0) then commit; end if; end loop; close icur; commit; exception when others then rollback; msg := 'err:'||substr(sqlerrm, 1, 90); insert into sm_err_msg(e_date, e_msg) values (sysdate, msg); commit;
Because the number of record is very large, so I want to commit every 1000 rows, but after commit for the first time, the cursor is not availible any more because of the for update clause in the cursor.
How to workaround it? Thanks.
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:89l4r19eqk9ngmficjbb80ske6pfg1qhp4_at_4ax.com...
> 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
Received on Wed Dec 28 2005 - 21:14:13 CST