Re: min(); never no_data_found

From: cate <catebekensail_at_yahoo.com>
Date: Tue, 22 Dec 2009 12:39:58 -0800 (PST)
Message-ID: <1221115a-04e2-43dd-b711-0f8ce8216a2e_at_n35g2000yqm.googlegroups.com>

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

Received on Tue Dec 22 2009 - 14:39:58 CST