Re: Store Procedure Questions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/06/10
Message-ID: <339e44e9.17358129@newshost>#1/1

On 4 Jun 1997 07:19:11 GMT, thonggie_at_mercury.starnet.gov.sg (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 .... into .... raises an exception if there is no data found since this is an error.

update ... set x = 5 where ... will use sql%notfound to indicate the warning that no rows were affected.

try:

   begin
    select x into y from t;
   exception
    when no_data_found then ....
   end;

to handle the error that occurrs when a select into fails to return exactly one row.   

> 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

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jun 10 1997 - 00:00:00 CDT