Prev: oracle 10g disaster recoveries
Next: best tutorial for installing oracle 10 for Solaris 10 x86
From: webtourist on 23 Mar 2010 12:45
10gR2:


given a function (in package "test") like this:

FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
l_ename VARCHAR2(90);
BEGIN
SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
RETURN l_ename ;
END ;

And a SQL function calls:

> select test.get_ename(7499) from dual;

TEST.GET_ENAME(7499)
------------------------
ALLEN

> select test.get_ename(74992) from dual;

TEST.GET_ENAME(74992)
-----------------------





I just realized the the above query wouldn't fail with "no_data_found"
- has this always been like this behavior ?
From: Michel Cadot on 23 Mar 2010 13:16

"webtourist" <webtourist(a)gmail.com> a �crit dans le message de news:
b2ae3703-61bc-480e-a8a9-c51e566aa11f(a)g11g2000yqe.googlegroups.com...
| 10gR2:
|
|
| given a function (in package "test") like this:
|
| FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
| l_ename VARCHAR2(90);
| BEGIN
| SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
| RETURN l_ename ;
| END ;
|
| And a SQL function calls:
|
| > select test.get_ename(7499) from dual;
|
| TEST.GET_ENAME(7499)
| ------------------------
| ALLEN
|
| > select test.get_ename(74992) from dual;
|
| TEST.GET_ENAME(74992)
| -----------------------
|
|
|
|
|
| I just realized the the above query wouldn't fail with "no_data_found"
| - has this always been like this behavior ?

Yes, no_data_found is trapped by SQL engine and converted to a NULL value.

Regards
Michel


From: Mladen Gogala on 23 Mar 2010 17:11
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?



--
http://mgogala.byethost5.com
From: ddf on 23 Mar 2010 17:32
On Mar 23, 12:45 pm, webtourist <webtour...(a)gmail.com> wrote:
> 10gR2:
>
> given a function (in package "test") like this:
>
>   FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
>    l_ename VARCHAR2(90);
>   BEGIN
>     SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
>     RETURN l_ename ;
>   END ;
>
> And a SQL function calls:
>
> > select test.get_ename(7499) from dual;
>
> TEST.GET_ENAME(7499)
> ------------------------
> ALLEN
>
> > select test.get_ename(74992) from dual;
>
> TEST.GET_ENAME(74992)
> -----------------------
>
> I just realized the the above query wouldn't fail with "no_data_found"
> - has this always been like this behavior ?

Where did you handle that exception? Possibly this is how you should
have coded your function:

FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
l_ename VARCHAR2(90);
BEGIN
SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
RETURN l_ename ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee with empno '||p_empno);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Data integrity error for empno '||
p_empno||': More than one employee assigned to this empno');
END ;


Then it would fail on NO_DATA_FOUND. Oracle did raise the exception
but there was nothing coded to properly handle it.


David Fitzjarrell
From: Maxim Demenko on 23 Mar 2010 18:16
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
 |  Next  |  Last
Pages: 1 2 3 4
Prev: oracle 10g disaster recoveries
Next: best tutorial for installing oracle 10 for Solaris 10 x86