User-Named Exception Handlers

The technique that is used to bind a numbered exception handler to a name using 'Pragma Exception_init()'.This binding of a numbered exception handler,to a name string, is done in the declare section of the PL/SQL block.

All objects declared in the declare section of a PL/SQL block are not created until actually required within the PL/SQL block. However, the binding of a numbered exception handler to a name must be done exactly when declared not when the exception handler is invoked due to an exception condition.

The Pragma action word is a call to a pre-compile, which immediately binds the numbered exception handler to a name when encountered.

The function Exception_init() takes two parameters the first is the user defined exception name the second is the Oracle engine's exception number. These lines will be included in the Declare section of the PL/SQL block.

Note: The user defined exception name must be the statement that immediately preceds the Pragma Exception_init() statement.

Syntax:

Declare exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(exception_name,error_code_no);
BEGIN

Using this technique it is possible to bind appropriate numbered exception handlers to names and use these names in the exception section of a PL/SQL block. When this is done the default exception handling code of the exception handler is overridden and the user-defined exception handling code is executed.

Syntax:

DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(exception_name,error_code_no);
BEGIN
--------
--------
EXCEPTION
WHEN exception_name THEN

END;

User-Named Exception Handlers

Pre-determined Internal PL/SQL exceptions

1. DUP_VAL_ON_INDEX: Raised when an insert or update attempts to create two rows with duplicate values in columns constrained by a unique index.

2. LOGIN_DENIED: Raised when an invalid username/password was used to log onto Oracle.

3. NO_DATA_FOUND: Raised when a select statement returns zero rows.

4. NOT_LOGGED_ON:Raised when PL/SQL issues an oracle call without being logged onto Oracle.

5. PROGRAM_ERROR: Raised when PL/SQL has an internal problem.

6. TIMEOUT_ON_RESOURCE: Raised when Oracle has been waiting to access a resource beyond the user-defined timeout limit.

7. TOO_MANY_ROWS: Raised when a select statement returns more than one row.

8. VALUE_ERROR: Raised when the data type or data size is invalid.

9. OTHERS: stands for all other exceptions not explicitly named

Pre-determined Internal PL/SQL exceptions

Exception Handler

An Exception Handler is nothing but a code block in memory that will attempt to resolve the current exception condition. The Oracle engine can recognize every exception condition that occurs in memory. To handle very common and repetitive exception conditions the Oracle engines uses Named Exception Handlers.

The Oracle engine has about fifteen to twenty named exception handlers. In addition to this the Oracle engine uses more than twenty thousand numbered exception handlers. These exception handlers are identified not by names but by four integers preceded by a hypen that is -1414. These exception handler names are actually a set of negative signed integers.

Each exception handler, irrespective of how it is identified (i.e. by Name or Number) has code attached that will attempt to resolve an exception condition. This is how Oracle's Default Exception-Handling strategy works.

The exception handler scans the PL/SQL block for the existence of an Exception section within the PL/SQL block. If an exception section within the PL/SQL block exists the exception handler scans the first word,after the action word When,within this exception section.

If the first word after the action word When,is the exception handler's name then the exception handler executes the code contained in the Thensection of the construct as follows.

Exception
When {Exception Name} Then {User defined action to be carried out}

The first word that follows the action word When must be 'String'.Hence this technique will work well for the fifteen to twenty 'named exception handlers'. In addition to these the Oracle engine has twenty thousand,numbered exception handlers,which are raised automatically and appropriately when the Oracle engine recognizes and exception condition. User defined exception handling code must be permitted even for these (numbered) exception handlers.

Exception Handler

Error Handling In PL/SQL

Every PL/SQL block of code encountered by the Oracle is accepted as a client. Hence the Oracle engine will make an attempt to execute every SQL sentence within the PL/SQL block. However while executing the SQL sentence anything can go wrong and the SQL sentence can fail.

When an SQL sentence fails the Oracle engine is the first to recognize this as an Exception condition.The Oracle engine immediately tries to handle the exception condition and resolve it. This is done by raising a built-in Exception Handler.

Error Handling In PL/SQL