Re: NO_DATA_FOUND exception workaround in functions?

A copy of this was sent to adil_at_msil.sps.mot.com (if that email address didn't require changing) On Wed, 15 Sep 1999 16:35:30 GMT, you 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?
>

if you are sure that the query will either return 0 or 1 rows (i mean SURE) then the following 'trick' might do it for you:

select MAX(c1), MAX(c2), MAX(c3) into v1, v2, v3   from T
 where .....

A query with aggregates and NO group by will always return 1 row. If no data would be returned -- a NULL row is returned. If 1 row is coming back -- we just select the MAX for that row (that is why it is IMPORTANT to be sure only 1 row can come back -- else you'll get only row row from the select but the column values in that one row could come from many different rows in the table!)

Here is an example:

tkyte_at_8.0> set feedback 1
tkyte_at_8.0> select max(dummy) from dual where 1=0;

M
-

1 row selected.

tkyte_at_8.0>

That selected 1 row -- a null row....

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

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 15 1999 - 13:52:56 CDT