PROGRAM THAT HANDLE MORE THAN ONE EXCEPTION IN ORACLE?

SQL> DECLARE

  2 NAME EMP.ENAME%TYPE;

  3 BEGIN

  4 SELECT ENAME INTO NAME FROM EMP WHERE

     DEPTNO=&DEPTNO;

  5 DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS '||NAME);

  6 EXCEPTION

  7 WHEN TOO_MANY_ROWS THEN

  8 DBMS_OUTPUT.PUT_LINE('MORE THAN ONE ROWS

     RETURNED');

  9 WHEN ZERO_DIVIDE THEN

 10 DBMS_OUTPUT.PUT_LINE('DIVIDE BY ZERO');

 11 WHEN OTHERS THEN

 12 DBMS_OUTPUT.PUT_LINE('NO ROWS EXIST');

 13 END;

 14 /

Enter value for deptno: 30

old   4: SELECT ENAME INTO NAME FROM EMP WHERE DEPTNO=&DEPTNO;

new   4: SELECT ENAME INTO NAME FROM EMP WHERE DEPTNO=30;

MORE THAN ONE ROWS RETURNED

PL/SQL procedure successfully completed.

SQL> @ MOREROWS.SQL;

Enter value for deptno: 40

old   4: SELECT ENAME INTO NAME FROM EMP WHERE DEPTNO=&DEPTNO;

new   4: SELECT ENAME INTO NAME FROM EMP WHERE DEPTNO=40;

NO ROWS EXIST

PL/SQL procedure successfully completed.