Friday, 21 June 2013

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.

No comments:

Post a Comment