Oracle 1Z0-183: The Aggregate That Always Returns a Row

The Aggregate That Always Returns a Row

The order-entry team owns p_dept_bonus(p_dept NUMBER) in the ORDER_MGMT schema. Its core is SELECT SUM(salary) INTO v_total FROM employees WHERE dept_id = p_dept;, followed by INSERT INTO bonus_audit(dept_id, amount) VALUES (p_dept, v_total * 0.1);. The exception section handles NO_DATA_FOUND by raising a friendly "department has no staff" error. Department 77 was created last week and currently has zero employees. Running the procedure for dept 77 does not raise the friendly error; it fails later with ORA-01400 (cannot insert NULL into BONUS_AUDIT.AMOUNT). Sixteen of the 210 production departments are empty today, and the nightly job aborts on the first one it meets. A developer proposes adding GROUP BY dept_id to "make the exception fire" and calls it a one-line fix.

1. Explain precisely why the NO_DATA_FOUND handler never fires for department 77, and why the error surfaces as ORA-01400 instead.

Read the worked answer

An aggregate with no GROUP BY is a single-row query by definition. It returns exactly one row even when the WHERE clause matches nothing, and SUM over an empty set is NULL. So SELECT SUM(salary) INTO v_total assigns NULL and completes normally: no NO_DATA_FOUND, and TOO_MANY_ROWS is impossible.

The failure simply relocates to the arithmetic. NULL * 0.1 is NULL, so the INSERT attempts to put NULL into a NOT NULL column and Oracle raises ORA-01400. The handler was written for the behaviour of a non-aggregate SELECT INTO, which is the wrong mental model here.

Diagnostic rule: any INTO fed by COUNT/SUM/AVG/MIN/MAX without GROUP BY can never raise NO_DATA_FOUND.

2. The developer wants to add `GROUP BY dept_id`. Evaluate that fix and give the version you would ship, stating which exceptions you would and would not code for.

Read the worked answer

GROUP BY dept_id would indeed make the query return zero rows for an empty department, so NO_DATA_FOUND would fire - technically it "works". It is still the wrong fix:

It converts an ordinary data condition (a department with no staff) into exception flow, which is slower and harder to read.
It silently changes the contract for every existing caller that expects a row.
The friendly message would then be produced from a handler rather than from an IF, mixing error and business logic.

What I would ship: keep one row guaranteed and test the value.

SELECT COUNT(*), NVL(SUM(salary), 0)
  INTO v_cnt, v_total
  FROM employees
 WHERE dept_id = p_dept;

IF v_cnt = 0 THEN
  RAISE_APPLICATION_ERROR(-20010, 'Department '||p_dept||' has no staff');
END IF;

INSERT INTO bonus_audit(dept_id, amount) VALUES (p_dept, v_total * 0.1);

NVL also protects against a department whose staff all have NULL salary. Exceptions to code for: none from this SELECT INTO (no NO_DATA_FOUND, no TOO_MANY_ROWS); let unexpected ones propagate.

3. The same schema has 40 other `SELECT ... INTO` statements. How would you find the ones carrying the same latent defect, without reading all 40?

Read the worked answer

Search for the structural signature, not the symptom: INTO paired with an aggregate and no GROUP BY, i.e. SELECT (COUNT|SUM|AVG|MIN|MAX)\s*\( in the same statement as INTO.

Why this finds real bugs and not noise: an aggregate INTO yields exactly one row, so any handler or IF c%NOTFOUND guard around it is dead code, and any arithmetic on the result inherits NULL.

The most damaging instance is the classic key generator SELECT MAX(id) INTO v_id FROM t; - on an empty table it returns NULL, not NO_DATA_FOUND, and the next INSERT fails on the primary key.

Practical checks:

Every aggregate INTO should be wrapped in NVL or paired with a COUNT(*).
Empty-set detection belongs in IF, not in EXCEPTION.
Add the pattern to code review / a PL/SQL checker rule so it does not reappear.