Handling Exceptions with PL/SQL

·         An exception is a PL/SQL error that is raised during program execution.

·         An exception can be raised:

                                I.            Implicitly by the Oracle server

                              II.            Explicitly by the program

·         An exception can be handled:

                                I.            By trapping it with a handler

                              II.            By propagating it to the calling environment

Handling Exception in PLSQL

Exception Types

·         Implicitly Raised:

o   Predefined Oracle server

o   Non-predefined Oracle server

·         Explicitly raised:

o   User-defined

Guidelines for Trapping Exceptions

•       The EXCEPTION keyword starts the exception handling section.

•       Several exception handlers are allowed.

•       Only one handler is processed before leaving the block.

•       WHEN OTHERS is the last clause.

•        

Trapping Predefined Oracle Server Errors

•       Reference the predefined name in the exception-handling routine.

•       Sample predefined exceptions:

–      NO_DATA_FOUND

–      TOO_MANY_ROWS

–      INVALID_CURSOR

–      ZERO_DIVIDE

–      DUP_VAL_ON_INDEX

Trapping Non-Predefined Oracle Server Errors

To trap Oracle server error number –01400 (“cannot insert NULL”):

Functions for Trapping Exceptions

•       SQLCODE: Returns the numeric value for the error code

•       SQLERRM: Returns the message associated with the error number

Trapping User-Defined Exceptions

Example of User defined exception:

RAISE_APPLICATION_ERROR Procedure

·         You can use this procedure to issue user-defined error messages from stored subprograms.

·         You can report errors to your application and avoid returning unhandled exceptions.

·         Used in two different places:

§  Executable section

§  Exception section

·         Returns error conditions to the user in a manner consistent with other Oracle server errors

Example of RAISE_APPLICATION_ERROR(Executable & Exception section):

BEGIN

...

  DELETE FROM employees

     WHERE  manager_id = v_mgr;

  IF SQL%NOTFOUND THEN

     RAISE_APPLICATION_ERROR(-20202,

       'This is not a valid manager');

  END IF;

   ...

   ...

EXCEPTION

   WHEN NO_DATA_FOUND THEN

     RAISE_APPLICATION_ERROR (-20201,

        'Manager is not a valid employee.');

END;
/