Re: min(); never no_data_found
Date: Tue, 22 Dec 2009 22:15:31 +0100
Message-ID: <4B3136F3.3050509_at_gmail.com>
On 22.12.2009 21:39, cate wrote:
> On Dec 22, 2:37 pm, cate<catebekens..._at_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 Received on Tue Dec 22 2009 - 15:15:31 CST