exception is not coming with sql max function

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 selectedSQL>

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 ONSQL> 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 ExceptionPL/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 ONSQL> 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 2PL/SQL procedure successfully completed.

Much simpler:

SELECT COALESCE((SELECT MAX(A_id) from table_A),   (SELECT MAX(A_id) from table_A_archive)) + 1FROM DUALINTO 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.