Re: SQL Function call does not fail on no_data_found ?
From: The Boss <usenet_at_No.Spam.Please.invalid>
Date: Wed, 24 Mar 2010 00:07:33 +0100
Message-ID: <4ba949b6$0$12223$e4fe514c_at_dreader12.news.xs4all.nl>
Maxim Demenko wrote:
> On 23.03.2010 22:11, Mladen Gogala wrote:
>> On Tue, 23 Mar 2010 18:16:23 +0100, Michel Cadot wrote:
>>
>>
>>> Yes, no_data_found is trapped by SQL engine and converted to a NULL
>>> value.
>>
>> Why is that? It looks like a huge bug to me. Is that documented
>> anywhere?
>
> Afair, what Michel stated, was always expected behaviour. There is a
> couple of references on MOS on this subject (is kinda of personal
> preferences whether it can be considered as oficially documented,
> however, i'm not sure, maybe it found already its way into Oracle
> manuals)
> Note 226211.1
> Note 258653.1
> Bug 893670
> Bug 299941
>
> Best regards
>
> Maxim
Date: Wed, 24 Mar 2010 00:07:33 +0100
Message-ID: <4ba949b6$0$12223$e4fe514c_at_dreader12.news.xs4all.nl>
Maxim Demenko wrote:
> On 23.03.2010 22:11, Mladen Gogala wrote:
>> On Tue, 23 Mar 2010 18:16:23 +0100, Michel Cadot wrote:
>>
>>
>>> Yes, no_data_found is trapped by SQL engine and converted to a NULL
>>> value.
>>
>> Why is that? It looks like a huge bug to me. Is that documented
>> anywhere?
>
> Afair, what Michel stated, was always expected behaviour. There is a
> couple of references on MOS on this subject (is kinda of personal
> preferences whether it can be considered as oficially documented,
> however, i'm not sure, maybe it found already its way into Oracle
> manuals)
> Note 226211.1
> Note 258653.1
> Bug 893670
> Bug 299941
>
> Best regards
>
> Maxim
The description of NO_DATA_FOUND in the PL/SQL User's Guide and Reference
con tains following 'disclaimer':
<q>
Because this exception is used internally by some SQL functions to signal
that they are finished, you should not rely on this exception being
propagated if you raise it within a function that is called as part of a
query.
</q>
Some pointers to discussions on the NO_DATA_FOUND exception:
- Best Practice article "On Exceptions and Rules" by Steven Feuerstein: http://www.oracle.com/technology/oramag/oracle/08-jul/o48plsql.html [second half of the article]
- Article "No Data Found: Bug or Feature" on Lewis Cunningham's blog: http://it.toolbox.com/blogs/oracle-guide/minitip-9-no-data-found-bug-or-feature-15602 [including some interesting comments]
- AskTom thread "NO_DATA_FOUND in Functions": http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10321465390114
- AskTom thread "CALL statement ignores NO_DATA_FOUND exception": http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:317260900346187160
HTH.
-- JeroenReceived on Tue Mar 23 2010 - 18:07:33 CDT