Re: NO_DATA_FOUND exception workaround in functions?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 15 Sep 1999 13:32:06 -0400
Message-ID: <NtXfN5=HvxojyJUmR=GHKo9Kj6YN@4ax.com>

On Wed, 15 Sep 1999 16:35:30 GMT, adil_at_msil.sps.mot.com wrote:

>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?

cursor-for loop might do the trick

   ...
   for c in ( select blah

                from foo ) loop
     l_blah := c.blah;
     exit;

   end loop;
   ...

This will either get back the first row that matches the constraints or no rows at all. This way you cannot get the NO_DATA_FOUND exception. It also avoids the TOO_MANY_ROWS exception that can arise with a select-into statement.

hope this helps.

chris.

>
>Thanks,
>
>Adi.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 15 1999 - 12:32:06 CDT