EXCEPTIONS - 3

[debasisdas]

EXAMPLE SHOWING USE OF USER DEFINED EXCEPTION
==============================================

DECLARE
M NUMBER(4);
MYERROR EXCEPTION;
BEGIN
SELECT COMM INTO M FROM EMP WHERE EMPNO=7788;
IF M IS NULL THEN
RAISE MYERROR;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ERROR OF DATA......!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('ERROR OF TOOMANY ROWS.....!');
WHEN MYERROR THEN
DBMS_OUTPUT.PUT_LINE('ERROR FOUND NULL......!');
END;
 

USER DEFINED EXCEPTION----Used to raise an exception explicitely. Must be declared in the declaration section and handled in the expeption block.

EXAMPLE SHOWING USE OF USER DEFINED EXCEPTION
==============================================

 DECLARE
 SALARY EMP.SAL%TYPE;
 NAME EMP.ENAME%TYPE;
 NO NUMBER:=&NO;
 GREATER EXCEPTION;   --User defined exception
 LESSER EXCEPTION;    --User defined exception
 BEGIN
 SELECT ENAME,SAL INTO NAME,SALARY FROM EMP WHERE  EMPNO=NO;
 IF SALARY>2000 THEN
 RAISE GREATER;   --User defined exception raised explicitely.
 ELSE
 RAISE LESSER;     --User defined exception raised explicitely.
 END IF;
 EXCEPTION
 WHEN GREATER THEN
 RAISE_APPLICATION_ERROR(-20001,'YOUR SALARY IS MORE  THAN 2000');
--User defined exception handled here.
 WHEN LESSER THEN
 RAISE_APPLICATION_ERROR(-20002,'YOUR SALARY IS LESS   THAN 2000');
--User defined exception handled here.
 WHEN NO_DATA_FOUND THEN
 RAISE_APPLICATION_ERROR(-20003,'PLEASE   ENTER A VALID  EMPNO');
 END;
 

NOTE--Both predeined and userdefined exceptions can be handled in a single exception block.

USER DEFINED EXCEPTION FOR ORACLE DEFINED NUMBER
=================================================

DECLARE
EXP1 EXCEPTION;
PRAGMA EXCEPTION_INIT(EXP1,-00001);   --exp1 is initialized to error number 00001
BEGIN
INSERT INTO DEPT VALUES(&DNO,'&DNAME','&LOC');
DBMS_OUTPUT.PUT_LINE('ONE RECORD INSERTED');
EXCEPTION
WHEN EXP1 THEN
DBMS_OUTPUT.PUT_LINE('DUPLICATE VALUE');
--when the exception 00001 is raised instead of showing oracle defined message user defined message is displayed.
END;
 
 

A pragma is a compiler directive that is processed at compile time, not at run time.
The pragma must appear somewhere after the exception declaration in the same declarative section.


Also check EXCEPTIONS - 4