PL/SQL ques. - SELECT ... FOR UPDATE

[JMac]

If I create an explicit CURSOR with a

CURSOR c1 IS
SELECT x,y,z
FROM a
WHERE x = nnn
FOR UPDATE;

OPEN c1 etc ...

Can I handle the fact that the target record might be locked, in such a way to warn the user (rather than have them sit and wait for the other session to commit or rollback)?

[JMac]

Found this on Ask Tom ...


select t.*, rowid from T
where t.c1 = :block.c1
and t.c2 = :block.c2
and ....
and t.rowid = :block.rowid
FOR UPDATE NOWAIT;

if that returns ORA-54 (resource busy), you are notified someone else has that row locked.

So I imagine If I cater for ORA-54 in my EXCEPTION handling section I can at least warn the user.

Any PL\SQL experts care to comment?

[padders]

Sounds fine. The EXCEPTION_INIT pragma is a useful way of associating names with given error numbers such that you can use the name (rather than the number) in the exception section.

DECLARE
  resource_busy EXCEPTION;
  PRAGMA EXCEPTION_INIT (resource_busy, -54);
BEGIN
  SELECT ...
  FROM   ...
  FOR UPDATE NOWAIT;
EXCEPTION
  WHEN resource_busy THEN
    do_something_else;
END;

In 9i you can use FOR UPDATE WAIT n; where n is the seconds to wait for a lock before raising error. If locks are held briefly this may avoid repeated attempts by the user to obtain the record.

Padders

[JMac]

Thanks Padders, looks useful.

In the Pragma directive - I presume the leading zeroes can be safely omitted - or is to best/expected to ommit them?

i.e
(resource_busy, -54);

is the same as

(resource_busy, -00054);?

[padders]

I don't believe it makes any difference. The standard package appears to pad to 4 digits in most cases, perhaps this is more readable.

Padders

[JMac]

Hmmm, need some PL\SQL help here ...

I DECLARE the EXCEPTION & Cursor:

resource_busy EXCEPTION;
PRAGMA EXCEPTION_INIT (resource_busy, -54);

CURSOR c1 IS
SELECT ...
FROM ...
FOR UPDATE WAIT 30;

BEGIN
OPEN c1;
FETCH c1 etc ...
UPDATE ...
SET ...
WHERE CURRENT OF c1;
EXCEPTION
WHEN resource_busy THEN
RAISE_APPLICATION_ERROR (-20001, ...
WHEN others THEN
RAISE_APPLICATION_ERROR (-20002, ...
END;

I lock the target record in another session, then run my PL\SQL. It waits 30 seconds then returns with Error -20002 and not - 20001 as expected.

What am I doing wrong?

[jmodic]

If you use WAIT N then the error code is ORA-30006, not ORA-54 as with NOWAIT....

[JMac]

Doh!!!