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: Scarab <yw_at_lucent.com>
Date: Thu, 29 Dec 2005 11:14:13 +0800
Message-ID: <dovk9n$md2@netnews.net.lucent.com>


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;

end;

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

Original text of this message

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