Re: Store Procedure Questions

From: Ken Denny <kdenny_at_interpath.com>
Date: 1997/06/10
Message-ID: <339D4ECB.1690@interpath.com>#1/1

Toh Hong Giep wrote:
>
> I have a store procedure which made use of the 'SQL%FOUND' and
> 'SQL%NOTFOUND' attributes after a select statement.
>
> However this doesn't seemed to work as expected. When there is no
> data, the store procedure will terminate abnormally giving the error that
> no data is found!! I did not have an exception statement for the store
> procedure. My code goes something like this:
>
> SELECT .....
> If SQL%FOUND then
> .....
> End if;
>
> Doesn't this handle the "exception" when there's no data selected. And
> if I were to include an exception statement at the bottom of the
> procedure, will the program continue to execute from the point where the
> exception happened, after the exception has been handled???
>
> Thanks for the invaluable help and advise. Regards to all

I have not found a use for SQL%FOUND and SQL%NOTFOUND. The way to do this is to use an exception clause, but don't put it at the end of the procedure. Instead, place a BEGIN ... EXCEPTION ... END; structure around every SELECT where you need to check whether it was found or not. If your code currently looks like this:

PROCEDURE xx IS

   <declarations>
BEGIN
   <beginning statements>
   SELECT ...
   IF SQL%FOUND THEN
      <found statements>
   ELSE
      <notfound statements>
   END IF;
   <rest of statements>
END; change it to:

PROCEDURE xx IS

   <declarations>
BEGIN
   <beginning statements>
   BEGIN

      SELECT ...
      <found statements>
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         <notfound statements>

   END;
   <rest of statements>
END; Hope this helps
Ken Denny
kdenny_at_interpath.com Received on Tue Jun 10 1997 - 00:00:00 CDT