exception is not coming with sql max function

Question

I wrote the below query expecting NO_DATA_FOUND exception to be thrown when no rows found.

BEGIN
  SELECT MAX(A_id) + 1 INTO id_variable from table_A;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  SELECT MAX(A_id) + 1 INTO id_variable from table_A_archive;
END;

there is no data in table_A but no exception was thrown and eventually the id_variable value is getting null. I google'd and noticed MAX function ignores null values but i could not find any remedy that can make it to throw exception.

How to make it to throw an exception so that control goes for exception and looks into archive table.

is there any other alternative than taking the count() and then getting the value only if count() > 0.

Answer 1

No, it won't go into exception. MAX will not raise no_data_found as it will return a NULL value.

See this:

SQL> select max(a_id) from table_a;

 MAX(A_ID)
----------


SQL> select a_id from table_a;

no rows selected

SQL>

is there any other alternative than taking the count() and then getting the value only if count() > 0.

You could have your custom exception and then raise it when the value is NULL.

For example,

SQL> CREATE TABLE table_A(a_id NUMBER);

Table created.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    id_variable NUMBER;
  3    is_null     EXCEPTION;
  4  BEGIN
  5    SELECT MAX(A_id) + 1 INTO id_variable FROM table_A;
  6    IF id_variable IS NULL THEN
  7      raise is_null;
  8    END IF;
  9  EXCEPTION
 10  WHEN is_null THEN
 11    DBMS_OUTPUT.PUT_LINE('Came into Exception');
 12  END;
 13  /
Came into Exception

PL/SQL procedure successfully completed.

SQL>

Update If you don't want to raise an exception and just want to select from another table when MAX returns NULL, then add an IF-ELSE block.

For example,

SQL> CREATE TABLE table_A(a_id NUMBER);

Table created.

SQL> CREATE TABLE table_b(a_id NUMBER);

Table created.

SQL> INSERT INTO table_b VALUES(1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    id_variable NUMBER;
  3  BEGIN
  4    SELECT max(A_id) + 1 INTO id_variable from table_A;
  5    IF id_variable IS NULL
  6    THEN
  7    SELECT A_id + 1 INTO id_variable FROM table_b;
  8    END IF;
  9    DBMS_OUTPUT.PUT_LINE('ID value is '||id_variable);
 10  END;
 11  /
ID value is 2

PL/SQL procedure successfully completed.

Answer 2

Much simpler:

SELECT COALESCE((SELECT MAX(A_id) from table_A), 
  (SELECT MAX(A_id) from table_A_archive)) + 1
FROM DUAL
INTO id_variable;

You still get a row back from the max query even if there are no rows, but the value is null - that's why there's no exception.

Use the fact that a null is returned to advantage by using coalesce(), which returns the first non-null value in the list.