Re: min(); never no_data_found

From: cate <>
Date: Tue, 22 Dec 2009 12:39:58 -0800 (PST)
Message-ID: <>

On Dec 22, 2:37 pm, cate <> 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
Received on Tue Dec 22 2009 - 14:39:58 CST

Original text of this message