Exceptions

The BEGIN section

Can contain variable assignments, Embedded SQL and calls to other functions and procedures.

A BEGIN-END block can contain nested DECLARE-BEGIN-END sub blocks.
The use of nested sub-blocks allows the use of local variables with limited scope.

Each plsql block should be terminated with / on a line by itself.

BEGIN
   code block
   /
EXCEPTION
   code block
   /
END;

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;

Standard Exceptions, from the the STANDARD package

Oracle Exception Name	Oracle Error	Explanation
DUP_VAL_ON_INDEX	ORA-00001	You attempted to create a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCE	ORA-00051	A resource timed out, took too long.
TRANSACTION_BACKED_OUT	ORA-00061	The remote portion of a transaction has rolled back.
INVALID_CURSOR	ORA-01001	The cursor does not yet exist. The cursor must be OPENed before any FETCH cursor or CLOSE cursor operation.
NOT_LOGGED_ON	ORA-01012	You are not logged on.
LOGIN_DENIED	ORA-01017	Invalid username/password.
NO_DATA_FOUND	ORA-01403	No data was returned
TOO_MANY_ROWS	ORA-01422	You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE	ORA-01476	Divide by zero error.
INVALID_NUMBER	ORA-01722	Converting a string to a number was unsuccessful.
STORAGE_ERROR	ORA-06500	Out of memory.
PROGRAM_ERROR	ORA-06501	Generic "Contact Oracle support" message.
VALUE_ERROR	ORA-06502	You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
ROWTYPE_MISMATCH	ORA-06504
CURSOR_ALREADY_OPEN	ORA-06511	The cursor is already open.
ACCESS_INTO_NULL	ORA-06530
COLLECTION_IS_NULL	ORA-06531