Re: PL/SQL Newbie Question

From: Paul Scott <aspscott_at_tcp.co.uk>
Date: Sat, 11 Sep 1999 01:47:36 +0100
Message-ID: <paTZNxgJ7VIbD9K4jD2cvx3hn+zT@4ax.com>

On Fri, 10 Sep 1999 23:33:47 GMT, khs_at_netset.com wrote:

>I have a SELECT statement that returns, at most, 1 row in a procedure.
>When the select doesn't return a row (NO_DATA_FOUND) I want to set the
>variable that recieves the column value to a space (' ') and go on
>with the rest of the logic in the procedure. What happens is that the
>procedure exits. How can I prevent this?
>
>Thanks for your help...

You have to trap the NO_DATA_FOUND exception in an exception handler. Once safely trapped, your code can continue without exiting.

Here's an example for you :

procedure MyProc(MyParameter in integer, AnOutput out integer) is
  MyVariable Char(1);
begin
  begin
    select
      MY_CHAR_FIELD
    into
      MyVariable
    from
      MY_TABLE
    where
      MY_PRIMARY_KEY = MyParameter;
  exception

     when NO_DATA_FOUND then
       MyVariable := ' ';

  end;
  /* Do some more code in here to do with your output parameter */ end;

Hope this helps.

Paul Scott Received on Fri Sep 10 1999 - 19:47:36 CDT