"Connor McDonald" <connor_mcdonald_at_yahoo.com> a écrit dans le message news:
400FC129.578C_at_yahoo.com...
> 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"
Thank you Connor for having spent a little of your time on it.
I'm glad to see that it works as expected !
Anyway I posted just to get a clue about that issue of NOWAIT
I don't actually need that feature, a waiting-lock is perfect for my
purpose.
But I was wondering . . . and I'm still wondering why I get this error.
Cheers,
--
Seb
Received on Thu Jan 22 2004 - 07:46:21 CST
