Some Of The Exception In Oracle Pl-Sql

EXCEPTIONS:
------------------

- NO_DATA_FOUND:
  --------------------------
   declare
 a number;
 begin
 select empno into a from emp where deptno=100;
 dbms_output.put_line(a);
 exception
 when no_data_found then
 dbms_output.put_line('DATA NOT FOUND');
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
END;
O/P: DATA NOT FOUND
SQLCODE: 100
SQLERRM:ORA-01403: no data found

- TOO_MANY_ROWS:
  ---------------------------

    declare

a number;
begin
select empno into a from emp where deptno=10;
dbms_output.put_line(a);
exception
when TOO_MANY_ROWS then
dbms_output.put_line('WE CANT EXECUTE MORE THAN ONE ROW');
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
end;

O/P: WE CANT EXECUTE MORE THAN ONE ROW
CODE: 1433

-- DUP_VAL_ON_INDEX:
   ------------------------------
    begin
INSERT INTO DEPT VALUES(10,'DATA','NO');
exception
when DUP_VAL_ON_INDEX then
dbms_output.put_line('U CANOT ENTER DUPLICATE VALUE IN DEPTNO');
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
end;

O/P: U CANOT ENTER DUPLICATE VALUE IN DEPTNO
CODE: 0001

-- ZERO_DIVIDE:
   ---------------------
    DECLARE
 A NUMBER;
 begin
 A:=10/0;
 exception
 WHEN ZERO_DIVIDE THEN
 DBMS_OUTPUT.PUT_LINE('DONT ENTER ZEROS IN DIVISION POSITION');
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
 when NO_DATA_FOUND then
 dbms_output.put_line('U CANOT ENTER DUPLICATE VALUE IN DEPTNO')
 WHEN DUP_VAL_ON_INDEX THEN
 dbms_output.put_line('ANOTHER EXCEPTION');
 WHEN OTHERS THEN
 dbms_output.put_line('ANOTHER TWO');
 end;
O/P: DONT ENTER ZEROS IN DIVISION POSITION
ERRCODE:-1476

--  ERRORS IN EXCEPTIONS:

         1  begin
  2  INSERT INTO DEPT VALUES(10,'DATA','NO');
  3  exception
  4  WHEN OTHERS THEN
  5  dbms_output.put_line('ANOTHER TWO');
  6  when NO_DATA_FOUND then
  7  dbms_output.put_line('U CANOT ENTER DUPLICATE VALUE IN DEPTNO');
  8  WHEN DUP_VAL_N_INDEX THEN
  9  dbms_output.put_line('ANOTHER EXCEPTION');
 10* end;
SQL> /
WHEN OTHERS THEN
*
ERROR at line 4:
ORA-06550: line 4, column 1:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

-- VALUE_ERROR:
   ---------------------

    DECLARE
 A NUMBER;
 begin
 SELECT ENAME INTO A FROM EMP WHERE ENAME LIKE 'KIN%';
 exception
 WHEN ZERO_DIVIDE THEN
 DBMS_OUTPUT.PUT_LINE('DONT ENTER ZEROS IN DIVISION POSITION');
 when NO_DATA_FOUND then
 dbms_output.put_line('U CANOT ENTER DUPLICATE VALUE IN DEPTNO');
 WHEN VALUE_ERROR THEN
 DBMS_OUTPUT.PUT_LINE('DATA TYPE ERROR');
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
 WHEN DUP_VAL_ON_INDEX THEN
 dbms_output.put_line('ANOTHER EXCEPTION');
 WHEN OTHERS THEN
 dbms_output.put_line('ANOTHER TWO');
 end;

O/P:- DATA TYPE ERROR
ERRCODE:-6502


 DECLARE
 L_SAL EMP.SAL%TYPE;
 BEGIN
 INSERT INTO DEPT VALUES(13,'RAISE','CURSOR');
 SELECT SAL INTO L_SAL FROM EMP WHERE EMPNO=7788;
 IF L_SAL>2000 THEN
 RAISE_APPLICATION_ERROR(-20150,'SAL IS HIGH');
 END IF;
 DBMS_OUTPUT.PUT_LINE('SAL IS'||L_SAL);
 END;


 DECLARE
 SEGMEN MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
 BEGIN
 DELETE FROM DEPT WHERE DEPTNO=90;
 SELECT SEGMENT1 INTO SEGMEN FROM MTL_SYSTEM_ITEMS_B
 WHERE SEGMENT1 LIKE 'SAINT%';
 IF SEGMEN='SAINT BERNARD' THEN
 RAISE_APPLICATION_ERROR(-20150,'USER NAME EXITS');
 END IF;
 DBMS_OUTPUT.PUT_LINE('SAL IS'||SEGMEN);
 END;