Re: NO_DATA_FOUND exception workaround in functions?

You could consider changing the implicit cursor select e_name into name
from emp where empno = p_empno
into an explicit cursor
cursor c_emp is
select e_name into name
from emp where empno = p_empno;
open c_empno;
fetch c_empno into some variable;
if c_empno%FOUND (or %NOTFOUND) etc.
close c_empno.
If this select occurs only once in your pl/sql I would wrap them in their own begin end block.

Hth,
--
Sybrand Bakker, Oracle DBA

<adil_at_msil.sps.mot.com> wrote in message news:7rohsh$lh7$1_at_nnrp1.deja.com...
> Hello,
>
> I have a function which, among other things, performs a query. This
> query sometimes returns no data (depending on the given arguments).
> Apparently, when this happens, a "NO_DATA_FOUND" exception is quietly
> thrown, and the function abruptly ends at this point. I'd rather this
> didn't happen, because I wish to continue even if the query returns no
> data. I could wrap each such query with in its own block, so that I
> could catch the exception right after the query, but that would be very
> cumbersome and could complicate my code significantly. I could also
> precede each select with another select to bring back the number of rows
> the upcoming select will produce, but that's even more cumbersome.
>
> Does anyone have a more elegant solution to this problem?
>
> Thanks,
>
> Adi.
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Wed Sep 15 1999 - 12:11:46 CDT