Exception Handling

What is an exception?

In PL/SQL, errors and warnings are called as exceptions. Whenever a predefined error occurs in a program, Pl/SQL rises an exception.

There are two types of exception handlers:

1.       Predefined internal exceptions
2.       Userdefined exceptions.

PL/SQL has a collection of predefined exceptions. Each has a name. These exceptions are automatically raised by PL/SQL whenever the corresponding error occurs.

When PL/SQL rises a predefined exception, program is aborted by displaying error message. But if program is to handle exception raised by PL/SQL then we have to use Exception Handler part of the block.

When Exception Handler is given, control is transferred to exception handler whenever there is an exception. After the exception handler is executed, control is transferred to next statement in the enclosing block.

Example of Exception handling:

DECLARE

                X_studid student.studid%type;

BEGIN

                SELECT studid, name FROM students

                WHERE name=’BHARAT PARMAR’;

EXCEPTION

                WHEN NO_DATA_FOUND Then

                INSERT INTO errortable VALUES(‘BHARAT PARMAR Not Found’);

END;

In above example, if no row found with the name ‘BHARAT PARMAR’ then PL/SQL raises exception NO_DATA_FOUND. Then this exception is handled by exception handler and one row is inserted into ERRORTABLE table.

PREDFINED EXCEPTIONS

PL/SQL has defined certain common errors and given names to these errors, which are called as predefined exceptions.

The following is the list of predefined exceptions and the corresponding Oracle Errors:

EXCEPTION NAME                                               ORACLE ERROR

CURSOR_ALREADY_OPEN                                      ORA-06511

DUP_VAL_ON_INDEX                                               ORA-00001

INVALID_CURSOR                                                     ORA-01001

LOGIN_DENIED                                                          ORA-01722

NO_DATA_FOUND                                                     ORA-01017

NOT_LOGGED_ON                                                    ORA-01403

PROGRAM_ERROR                                                    ORA-01012

ROW_TYPE_MISMATCH                                           ORA-06501

STORAGE_ERROR                                                     ORA-06504

TIMEOUT_ON_RESOURCE                                      ORA-06500

TOO_MANY_ROWS                                                  ORA-01422

VALUE_ERROR                                                           ORA-06502

ZERO_DIVIDE                                                             ORA-01476

USER DEFINED EXCEPTION:

PL/SQL allows you to create exceptions of your own. Unlike predefined exceptions, user defined exceptions are to be raised explicitly using RAISE command.

Syntax:

RAISE exception;

Once a user defined exception is raised using RAISE command, it is to be handled just like a predefined exception. The following are the steps of userdefined exceptions:

·         Declare an excpetion in DECLARE section using keyword EXCEPTION.

·         Use RAISE command to raise the exception in the executable part.

·         Handle userdefined exception in exception handler.