Re: stupid newbbie question
Date: Tue, 08 Dec 1998 15:41:55 -0800
Message-ID: <366DB943.D234F4EF_at_cat.e-mail.NOSPAM.com>
First of all, a "select count(*)" should never trigger a no_data_found exception. There must be something else triggering the exception.
Your thinking on this is logical, although it will result in extra hits to the database every time you "select count".
I deal with this issue by encapsulating select statements inside of nested BEGIN ... END blocks. You can then test the implicit sql%rowcount cursor variable to see if any rows were actually selected (sql%found and sql%notfound would probably work also).
Example
DECLARE
dummy_var varchar2(1);
BEGIN
BEGIN
/* this will fail */
select 'X' into dummy_var from dual where dummy = 'nonsense';
/* put code here to execute when data is found */
.
.
EXCEPTION
/* something you can do if you just want to get out of this BEGIN/END
block */
when no_data_found then null;
END;
/* here's a test you can now make if there is additional processing you want to
do depending on whether data was found above or not
*/
if sql%rowcount = 0 then
/* no data was found above */
else
/* data was found above */
end if;
END;
Kent Eilers wrote:
> I writing some PL/SQL procedures and functions and have come up against
> a problem whenever a query returns no rows (ala the ORA-01403: no data
> found error message). I've looked in three books to get a strategy on
> dealing with this and have come up with zilch.
>
> Basically I do not want to go to an exception handler when this
> happens. I thought I had escaped this drainhole by first running a
> query to dump the count(*) into a local variable. But this still
> triggers the 1403 error.
>
> What do all you PL/SQL pro's do? My (rather limited) understanding of
> the exception handler is you cannot 'on error resume....' so how do deal
> with this?
>
> Any response greatly appreciated!
Received on Wed Dec 09 1998 - 00:41:55 CET