Using FOR UPDATE in PL/SQL

From: Seb <sebastien-louchart_at_HAKUNA_SPAMwanadoo.fr>
Date: Thu, 22 Jan 2004 10:10:54 +0100
Message-ID: <buo42v$fns1@news.rd.francetelecom.fr>

Hello,

First the basics ;)
Oracle version 8.1.7.4
OS Solaris 5.8 on a V880

Then my issue :
I'm writing a package procedure which uses a cursor as part of its job. That cursor is supposed to lock the rows it acquires so I declare it with FOR UPDATE
(my code below)

cursor c_for_locking(param1 NUMBER, param2 NUMBER) RETURN mytable%ROWTYPE
IS SELECT * FROM mytable
WHERE field1=param1 AND field2=param2
FOR UPDATE; I use it in a LOOP FETCH structure containing an UPDATE statement

UPDATE mytable SET ...
WHERE CURRENT OF c_for_locking;

Everything's just gone fine till this point.

As I don't want Oracle to wait for the rows to be released in case of a lock,
I would like to set NOWAIT in the FOR UPDATE clause of my cursor declaration.

I do it ...
I replace my pkg body, Oracle tells me there are some compilation errors And I get the following
LINE/COL ERROR

-------- -------------------------------------------------------------------
---------------------------

387/28 PLS-00404 cursor 'c_for_locking' must be declared with FOR UPDATE to use with CURRENT OF
368/3 PL/SQL: SQL Statement ignored

I RTFM for the action to take to handle this error. It simply tells me to add FOR UPDATE !
What's the trouble with NOWAIT ?
What did I miss ??

TIA

--
Seb

Received on Thu Jan 22 2004 - 03:10:54 CST