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 
