Re: Using FOR UPDATE in PL/SQL

Seb wrote:
>
> 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

This done on 9204

SQL> declare
  2 cursor c is select * from t for update nowait;   3 begin
  4 for i in c loop
  5 update t set created = sysdate
  6 where current of c;
  7 end loop;
  8 end;
  9 /

PL/SQL procedure successfully completed.

so it should work as you anticipate. In any event, can the whole thing just be replaced with an update statement (ie no plsql at all)

hth
connor

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"