8,127
==============================================
Expand|Select|Wrap|Line Numbers
- 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;
EXAMPLE SHOWING USE OF USER DEFINED EXCEPTION
==============================================
Expand|Select|Wrap|Line Numbers
- 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;
USER DEFINED EXCEPTION FOR ORACLE DEFINED NUMBER
=================================================
Expand|Select|Wrap|Line Numbers
- 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;
The pragma must appear somewhere after the exception declaration in the same declarative section.
Also check EXCEPTIONS - 4
