Aggregation Functions and NO_DATA_FOUND Exception In Oracle

here are some important points when using aggregation functions. Although you have no data, this functions(MAX, MIN etc.) return  row and if you add NO_DATA_FOUND exception, exception block never executes.

Aggregation functions al least return one record when your table is empty except

• when you use HAVING clause
• when you use GROUP BY expression

Otherwise they return one NULL record.


I demonstrate a small example to make clear:

DECLARE
a VARCHAR2(12) := '';
b NUMBER;
BEGIN

SELECT COUNT(a.dummy)
INTO b
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T';
dbms_output.put_line('COUNT(a.dummy) without MAX is ' || b);

SELECT COUNT(*)
INTO b
FROM (SELECT MAX(a.dummy)
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T');
dbms_output.put_line('COUNT(a.dummy) with MAX is ' || b);

---
BEGIN
SELECT MAX(a.dummy)
INTO a
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T';
dbms_output.put_line('MAX(a.dummy)[No HAVING, No GROUP BY] passed..');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in MAX(a.dummy) [No HAVING, No GROUP BY]...');
END;

---
BEGIN
SELECT MAX(a.dummy)
INTO a
FROM (SELECT * FROM dual) a
HAVING MAX(a.dummy) = 'T';
dbms_output.put_line('MAX(a.dummy) with HAVING passed..');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in MAX(a.dummy) with HAVING...');
END;

---
BEGIN
SELECT a.dummy
INTO a
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T'
GROUP BY a.dummy;
dbms_output.put_line('a.dummy with group by passed..');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in a.dummy with group by...');
END;

---
BEGIN
SELECT a.dummy
INTO a
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T';
dbms_output.put_line('a.dummy passed..');

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in a.dummy...');
END;

END;




The output is

COUNT(a.dummy) without MAX is 0
COUNT(a.dummy) with MAX is 1
MAX(a.dummy)[No HAVING, No GROUP BY] passed..
no_data_found exception in MAX(a.dummy) with HAVING...
no_data_found exception in a.dummy  with group by...
no_data_found exception in a.dummy...