I've come across the following scenario in work. Consider the following block:
Begin
Select Max(Column) from Table where some condition;
Exception
When No_Data_Found then
log into an audit table
End;
when there are actually no rows found, the control of execution is not getting diverted into the exception block but is returning NULL.
When tried executing without the MAX function, the control of execution is caught in the exception block.
Can anyone please explain this behaviour?
MAX is an aggregate function. Aggregate functions perform a calculation on a set of values and return a single value. They always return a single value per group (if you don't have a GROUP BY clause, you have one group).
Therefore, in your code, you'll never have NO_DATA_FOUND because MAX will always return one value - could be null. If you take out MAX, it is a regular select, which may result in no rows selected, thereby going into the NO_DATA_FOUND block.
Pure aggregate functions--without corresponding grouped-by columns--always return exactly one row.
SQL> desc emp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> SET NULL [NULL]
SQL> SELECT MAX(sal) FROM emp WHERE 1 = 0;
MAX(SAL)
----------
[NULL]
SQL>
