PL/SQL ques. - SELECT ... FOR UPDATE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

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

Hybrid View

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

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

    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)?

  2. #2
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?

  3. #3
    Join Date
    Jan 2004
    Posts
    162
    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.
    Code:
    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

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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);?

  5. #5
    Join Date
    Jan 2004
    Posts
    162
    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

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?
    Last edited by JMac; 02-25-2004 at 10:24 AM.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    If you use WAIT N then the error code is ORA-30006, not ORA-54 as with NOWAIT....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Doh!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width