Prev: min(); never no_data_found
Next: Looks like 11.2 now available on hpux and aix
From: cate on 22 Dec 2009 15:37
How can you make min() throw no_data_found?


select min(datex) from tablex where x = 1;

With min(), I never get a no_data_found exception.

From: cate on 22 Dec 2009 15:39
On Dec 22, 2:37 pm, cate <catebekens...(a)yahoo.com> wrote:
> How can you make min() throw no_data_found?
>
> select min(datex) from tablex where x = 1;
>
> With min(), I never get a no_data_found exception.

The check is useless because the IF condition is tested only when
%NOTFOUND is false. When PL/SQL raises NO_DATA_FOUND, normal execution
stops and control transfers to the exception-handling part of the
block.

However, a SELECT INTO statement that calls a SQL aggregate function
never raises NO_DATA_FOUND because aggregate functions always return a
value or a null. In such cases, %NOTFOUND yields FALSE, as the
following example shows:

BEGIN
...
SELECT MAX(sal) INTO my_sal FROM emp WHERE deptno = my_deptno;
-- never raises NO_DATA_FOUND
IF SQL%NOTFOUND THEN -- always tested but never true
... -- this action is never taken
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN ... -- never invoked
From: Jeremy on 23 Dec 2009 10:10
In article <79bb2ee5-f28a-43b9-af1e-4597012495f0
@a32g2000yqm.googlegroups.com>, Mark.Powell2(a)hp.com says...

>
> Cate, why not test for a NULL value being returned and if so the RAISE
> NO_DATA_FOUND?
>

What's the opinion of this ng's contributors on the advisability of
raising "standard" error conditions when the underlying reasons for
raising that error are different?

To me it seems like a practice not to be recommended - potential for
misleading people unfamiliar with the code later on in maintenance mode.

--
jeremy

From: Mark D Powell on 23 Dec 2009 08:50
On Dec 22, 4:15 pm, Maxim Demenko <mdeme...(a)gmail.com> wrote:
> On 22.12.2009 21:39, cate wrote:
>
>
>
>
>
> > On Dec 22, 2:37 pm, cate<catebekens...(a)yahoo.com>  wrote:
> >> How can you make min() throw no_data_found?
>
> >> select min(datex) from tablex where x = 1;
>
> >> With min(), I never get a no_data_found exception.
>
> > The check is useless because the IF condition is tested only when
> > %NOTFOUND is false. When PL/SQL raises NO_DATA_FOUND, normal execution
> > stops and control transfers to the exception-handling part of the
> > block.
>
> > However, a SELECT INTO statement that calls a SQL aggregate function
> > never raises NO_DATA_FOUND because aggregate functions always return a
> > value or a null. In such cases, %NOTFOUND yields FALSE, as the
> > following example shows:
>
> > BEGIN
> >     ...
> >     SELECT MAX(sal) INTO my_sal FROM emp WHERE deptno = my_deptno;
> >        -- never raises NO_DATA_FOUND
> >     IF SQL%NOTFOUND THEN  -- always tested but never true
> >        ...  -- this action is never taken
> >     END IF;
> > EXCEPTION
> >     WHEN NO_DATA_FOUND THEN ...  -- never invoked
>
> It is unclear for me, what a business goal are you behind? If you know,
> that aggregate functions always return value or null, why don't you
> write your code in such a manner, that this circumstance is accounted?
> But if you need by any means raise a no_data_found by aggregate
> functions, maybe that helps...
>
> SQL> declare
>    2  my_sal number;
>    3  my_deptno number := -1;
>    4  begin
>    5   select max(sal) into my_sal from emp where deptno = my_deptno
> group by 1;
>    6  exception
>    7   when no_data_found then
>    8   if sql%notfound then
>    9    dbms_output.put_line('tests should be done thoroughfull');
>   10   end if;
>   11   dbms_output.put_line('never say never');
>   12  end;
>   13  /
> tests should be done thoroughfull
> never say never
>
> PL/SQL procedure successfully completed.
>
> Best regards
>
> Maxim- Hide quoted text -
>
> - Show quoted text -

Cate, why not test for a NULL value being returned and if so the RAISE
NO_DATA_FOUND?

> @t20
> set echo on
> set serveroutput on
1> declare
2 v_variable varchar2(10);
3 begin
4 select 'X' into v_variable from dual;
5 if v_variable is null
6 then RAISE NO_DATA_FOUND;
7 end if;
8 dbms_output.put_line('Found Data');
9 exception
10 when NO_DATA_FOUND then dbms_output.put_line('NO DATA');
11 end;
12 /
Found Data

PL/SQL procedure successfully completed.

1 > declare
2 v_variable varchar2(10);
3 begin
4 select NULL into v_variable from dual;
5 if v_variable is null
6 then RAISE NO_DATA_FOUND;
7 end if;
8 dbms_output.put_line('Found Data');
9 exception
10 when NO_DATA_FOUND then dbms_output.put_line('NO DATA');
11 end;
12 /
NO DATA

PL/SQL procedure successfully completed.

HTH -- Mark D Powell --
From: Mark D Powell on 25 Dec 2009 22:51
On Dec 23, 10:10 am, Jeremy <jeremy0...(a)gmail.com> wrote:
> In article <79bb2ee5-f28a-43b9-af1e-4597012495f0
> @a32g2000yqm.googlegroups.com>, Mark.Powe...(a)hp.com says...
>
>
>
> > Cate, why not test for a NULL value being returned and if so the RAISE
> > NO_DATA_FOUND?
>
> What's the opinion of this ng's contributors on the advisability of
> raising "standard" error conditions when the underlying reasons for
> raising that error are different?
>
> To me it seems like a practice not to be recommended - potential for
> misleading people unfamiliar with the code later on in maintenance mode.
>
> --
> jeremy


You have a point. I normally use the 20xxx error codes Oracle
reserved by Oracle for customer application error codes but my post
just adresses one way to do what the OP asked.

HTH -- Mark D Powell --


 |  Next  |  Last
Pages: 1 2
Prev: min(); never no_data_found
Next: Looks like 11.2 now available on hpux and aix