Re: [Fwd: PL/SQL - Commits in a update cursor]

From: Gordon Laqua <glaqua_at_harborsys.com>
Date: Wed, 28 Jul 1999 19:50:23 GMT
Message-ID: <3aJn3.9$Ki5.4165880@nnrp1.cal.metronet.ca>

From the oracle 8 doc

"Remember, the FOR UPDATE clause acquires exclusive row locks. All rows are locked when you open the cursor, and they are unlocked when you commit your transaction. So, you cannot fetch from a FOR UPDATE cursor after a commit. If you do, PL/SQL raises an exception. "

"If you want to fetch across commits, do not use the FOR UPDATE and CURRENT OF clauses. Instead, use the ROWID pseudocolumn to mimic the CURRENT OF clause. Simply select the rowid of each row into a ROWID variable. Then, use the rowid to identify the current row during subsequent updates and deletes. "

In article <379F5183.51FA5FC0_at_ix.netcom.com>, CJ <djjr_at_ix.netcom.com> wrote:
>Anyone have any sample code on how to commit every X,000 records in a
>update cursor?
>I get the following error : ORA-01002: fetch out of sequence
>Can it be done? My code looks something like this :
>
>For r_rec in rec_cursor loop
> /* Updates here */
>
> rec_cnt := rec_cnt + 1;
> if (rec_cnt mod 10000=0)
> commit;
>end if
>end loop;

---

Gordon Laqua, Systems Consultant | http://www.harborsys.com/ Beta Systems of Canada Ltd. | http://www.betasystems.com/ Disclaimer: If I spoke for the company,...... Received on Wed Jul 28 1999 - 14:50:23 CDT