Re: How to nowait conditions in the SQL

From: Lothar Armbrüster <lothar.armbruester_at_rheingau.netsurf.de>
Date: 17 Nov 98 20:15:14 +0100
Message-ID: <1255.625T729T12153468@rheingau.netsurf.de>

On 17-Nov-98 16:37:22 rshanker_m wrote:

>How do I put the nowait conditions in the SQL(PL/SQL) if I
>want to skip the processing of the current statement
>in case there is a lock on the table(s) which I want to use.

Hello out there,

you will have to put the statement(s) into an begin...exception block. Like this:

...

for my_rec in my_cur loop

   begin

      /* Lock the row to be processed. This will cause an ORA-00054
         when the row is already locked.
         The following select has to give just one row or you
         must do the locking differently (maybe select count(...)? ) */
      select a_column into a_variable
      from a_table
      where a_column=my_rec.a_column
      for update nowait;

      /* Do some processing */
      update a_table ...
   exception
      /* You will have to capture the ORA-00054 here,
         but I don't know the exact exception name for that ;-) */
      when others then
         null;

   end;
end loop;

Also beware that I'm writing this at home with no Oracle at hand. So the actual syntax may differ slightly.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |

Received on Tue Nov 17 1998 - 13:15:14 CST