Re: Very strange problem.

On Wed, 28 Dec 2005 15:38:49 +0800, "Scarab" <yw@xxxxxxxxxx> 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
.

Relevant Pages

  • Re: Opinions on approach, please...
    ... Code conversion is much more tricky. ... to update more than 32k records without a commit in-between. ... I advise you to do cursor definitions on working storage). ... I don't want to see ANY SQL code in them. ...
    (comp.lang.cobol)
  • Re: Very strange problem.
    ... end loop; ... 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 ... >>cursor icur is ...
    (comp.databases.oracle.server)
  • [git pull] drm
    ... This is mostly KMS related fixes, with a GEM related fix that stable also ... commit a75f28419a7efff611a81293d41d0e2137d7591e ... For KMS drivers, we really need to cleanup the driver before disabling ... Pin cursor bo and unpin old bo when setting cursor. ...
    (Linux-Kernel)
  • Re: Performance improvment -loop update.
    ... For c1_rec in c1 Loop ... table Ems_Currentsoexpedite_Tmp is having 8000 records and cursor ... of a "Job Jar" that only seemed to return one job for him to do ...
    (comp.databases.oracle.tools)
  • Re: open cursor with hold question
    ... James D. White wrote: ... A commit on the second DB handle ... >> How could I declare an open cursor with hold in a perl DBI program? ...
    (perl.dbi.users)