Re: min(); never no_data_found
Date: Tue, 22 Dec 2009 12:39:58 -0800 (PST)
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:
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