Exceptions

Oracle includes about 20 predefined exceptions (errors) - we can allow Oracle to raise these implicitly.

For errors that don't fall into the predefined categories - declare in advance and allow oracle to raise an exception.

For problems that are not recognised as an error by Oracle - but still cause some difficulty within your application - declare a User Defined Error and raise it explicitly
i.e IF x >20 then RAISE ...

Syntax:

EXCEPTION
   WHEN exception1 [OR exception2...]] THEN
   ...
   [WHEN exception3 [OR exception4...] THEN
   ...]
   [WHEN OTHERS THEN
   ...]

Where exception is the exception_name e.g.
WHEN NO_DATA_FOUND...
Only one handler is processed before leaving the block.

Trap non-predefined errors by declaring them
You can also associate the error no. with a name so that you
can write a specific handler. 
This is done with the PRAGMA EXCEPION_INIT pragma.

PRAGMA (pseudoinstructions) indicates that an item is 
a 'compiler directive' Running this has no immediate effect
but causes all subsequent references to the exception name 
to be interpreted as the associated Oracle Error.

-

Trapping a non-predefined Oracle server exception

DECLARE
   -- name for exception
   e_emps_remaining EXCEPTION
   PRAGMA_EXCEPTION_INIT (
      e_emps_remaining, -2292);
   v_deptno dept.deptno%TYPE :=&p_deptno;

BEGIN
   DELETE FROM dept
   WHERE deptno = v_deptno
   COMMIT;
EXCEPTION
   WHEN e_emps_remaining THEN
   DBMS_OUTPUT.PUT_LINE ('Cannot remove dept '||
   TO_CHAR(v_deptno) || '. Employees exist. ');
END;

-

When an exception occurs you can identify the 
associated error code/message with two supplied 
functions SQLCODE and SQLERRM

SQLCODE - Number
SQLERRM - message

An example of using these:
DECLARE
   v_error_code NUMBER;
   v_error_message VARCHAR2(255);

BEGIN

   ...

EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
   v_error_code := SQLCODE
   v_error_message := SQLERRM
   INSERT INTO t_errors
   VALUES ( v_error_code, v_error_message);
END;

-

Trapping user-defined exceptions

DECLARE the exception
RAISE the exception
Handle the raised exception

e.g.
DECLARE
  e_invalid_product EXCEPTION
BEGIN
   update PRODUCT
   SET descrip = '&prod_descr'
   WHERE prodid = &prodnoumber';
   IF SQL%NOTFOUND THEN
     RAISE e_invalid_product;
   END IF;
   COMMIT;
EXCEPTION
   WHEN e_invalid_product THEN
   DBMS_OUTPUT.PUT_LINE ('INVALID PROD NO');
END;

-

Propagation of Exception handling in sub blocks

If a sub block does not have a handler for a 
particular error it will propagate to the 
enclosing block - where it can be caught by 
more general exception handlers.

-

RAISE_APPLICATION_ERROR (error_no, message[,{TRUE|FALSE}]);

This procedure  allows user defined error 
messages from stored sub programs - call only from stored sub prog.
error_no = a user defined no (between -20000 and -20999)

TRUE = stack errors
FALSE = keep just last

This can either be used in the executable section of code or 
the exception section

e.g.
EXCEPTION
   WHEN NO_DATA_FOUND THEN
   RAISE_APPLICATION_ERROR (-2021,
        'manager not a valid employee');
END;

Related :