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.
Comments
Post a Comment