Prev: there is an error in comments
Next: min(); never no_data_found
From: Maxim Demenko on 22 Dec 2009 16:15
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
 | 
Pages: 1
Prev: there is an error in comments
Next: min(); never no_data_found