Oracle PL/SQL Exceptions Handling

How Oracle PL/SQL Exceptions Propagate?

A PL/SQL program is an anonymous block, a procedure, or a function. This program can call other procedures or functions, or nest an anonymous block within that block. So at any given point in execution, there might be several layers of PL/SQL blocks nested within other blocks. Each PL/SQL block can have its own exception section, or it can be totally void of exception handlers.

To determine the appropriate exception-handling behavior, PL/SQL follows rules regarding:

• Scope: The PL/SQL block or blocks in which an exception can be raised and handled.
• Propagation: The way in which an exception is passed back through enclosing blocks until it is handled or is resolved to be an unhandled exception.

When an exception is raised, if PL/SQL cannot find a handler for it in the current block or subprogram, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. If no handler is found, PL/SQL returns an unhandled exception error to the host environment.

Exceptions cannot propagate across remote procedure calls done through database links. A PL/SQL block cannot catch an exception raised by a remote subprogram.

The following three Figures illustrate the basic exception propagation rules defined in the Oracle PL/SQL.

Figure 1 Propagation Rules: Example 1

In the above figure 1, the innermost block has an exception handling section, when exception A is raised, PL/SQL first checks to see if the exception A can be handled in this section. PL/SQL scans the exception handlers and finds that it is handled in this block locally, after which the execution resumes in the enclosing block.

Figure 2 Propagation Rules: Example 2

In the above figure 2, when exception B is raised, PL/SQL first checks to see if the exception B can be handled in the innermost section. Because it was not handled, PL/SQL closes that block and raises the exception B in the enclosing block. Control immediately passes to the outer exception handling section. PL/SQL scans the exception handlers and finds that it is handled in this block, so the code for that handler is executed, after which control passes passes to the host environment.

Figure 3 Propagation Rules: Example 3

In the above figure 3, when exception C is raised, PL/SQL scans all the exception handlers and finds that it can't be handled, so an unhandled exception is returned to the host environment.

An exception can propagate beyond its scope, that is, beyond the block in which it was declared. Consider the following example:

BEGIN
   DECLARE  ---------- sub-block begins
     past_due EXCEPTION;
     due_date DATE := trunc(SYSDATE) - 1;
     todays_date DATE := trunc(SYSDATE);
   BEGIN
     IF due_date < todays_date THEN
        RAISE past_due;
     END IF;
   END;  ------------- sub-block ends
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
END;
/

Because the block that declares the exception past_due has no handler for it, the exception propagates to the enclosing block. But the enclosing block cannot reference the name PAST_DUE, because the scope where it was declared no longer exists. Once the exception name is lost, only an OTHERS handler can catch the exception. If there is no handler for a user-defined exception, the calling application gets this error:

ORA-06510: PL/SQL: unhandled user-defined exception

Oracle Predefined PL/SQL Exceptions

The the following is a list of PL/SQL predefined exceptions:

ACCESS_INTO_NULL - ORA-06530 - Attempted to assign values to the attributes of an uninitialized (NULL) object.

CASE_NOT_FOUND - ORA-06592 - None of the choices in the WHEN clauses of a CASE statement is selected and there is no ELSE clause

COLLECTION_IS_NULL - ORA-06531 - Attempt to apply collection methods other than EXISTS to an uninitialized (NULL) PL/SQL table or varray

CURSOR_ALREADY_OPEN - ORA-06511 - Exactly what it seems to be. Tried to open a cursor that was already open

DUP_VAL_ON_INDEX - ORA-00001 - An attempt to insert or update a record in violation of a primary key or unique constraint

INVALID_CURSOR - ORA-01001 - The cursor is not open or not valid in the context in which it is being called

INVALID_NUMBER - ORA-01722 - It isn't a number even though you are treating it like one to trying to turn it into one

LOGIN_DENIED - ORA-01017 - Invalid name and/or password for the instance

NO_DATA_FOUND - ORA-01403 - The SELECT statement returned no rows or referenced a deleted element in a nested table or referenced an initialized element in an Index-By table

NOT_LOGGED_ON - ORA-01012 - You lost your connection to the database

PROGRAM_ERROR - ORA-06501 - Internal PL/SQL error

ROWTYPE_MISMATCH - ORA-06504 - The rowtype does not match the values being fetched, or assigned, to it

SELF_IS_NULL - ORA-30625 - Program attempted to call a MEMBER method, but the instance of the object type has not been intialized. The built-in parameter SELF points to the object, and is always the first parameterpassed to a MEMBER method

STORAGE_ERROR - ORA-06500 - A hardware problem: Either RAM or disk drive

SUBSCRIPT_BEYOND_COUNT - ORA-06533 - Reference to a nested table or varray index higher than the number of elements in the collection

SUBSCRIPT_OUTSIDE_LIMIT - ORA-06532 - Reference to a nested table or varray index outside the declared range (such as -1)

SYS_INVALID_ROWID - ORA-01410 - The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid

TIMEOUT_ON_RESOURCE - ORA-00051 - The activity took too long and timed out

TOO_MANY_ROWS - ORA-01422 - The SQL INTO statement brought back more than one value or row (only one is allowed)

VALUE_ERROR - ORA-06502 - An arithmetic, conversion, truncation, or size-constraint error. Usually raised by trying to cram a 6 character string into a VARCHAR2(5) variable

ZERO_DIVIDE - ORA-01476 - Not only would your math teacher not let you do it. Computer's won't either. Who said you didn't learn anything useful in primary school

Handling the Unnamed System Exceptions

Beside the above predefined system exceptions, those system exceptions for which Oracle does not provide a name is known as unamed system exception. These exception do not occur frequently. These Exceptions have a code and an associated message.

There are two ways to handle unnamed sysyem exceptions:

1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.

We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT.  EXCEPTION_INIT will associate a predefined Oracle error number to a programmer_defined exception name.

Steps to be followed to use unnamed system exceptions are

• They are raised implicitly.
• If they are not handled in WHEN Others they must be handled explicity.
• To handle the exception explicity, they must be declared using Pragma EXCEPTION_INIT as given above and handled referecing the user-defined exception name in the exception section.

The general syntax to declare unnamed system exception using EXCEPTION_INIT is:

DECLARE

   exception_name EXCEPTION; 

   PRAGMA EXCEPTION_INIT (exception_name, Err_code); 

BEGIN 

--Execution section

EXCEPTION

  WHEN exception_name THEN

     handle the exception

END;

 

Example

Lets consider the product table and order_items table from sql joins.

Here product_id is a primary key in product table and a foreign key in order_items table.
If we try to delete a product_id from the product table when it has child records in order_id table an exception will be thrown with oracle code number -2292.

We can provide a name to this exception and handle it in the exception section as given below.

 DECLARE 

  Child_rec_exception EXCEPTION; 

  PRAGMA EXCEPTION_INIT (Child_rec_exception, -2292); 

BEGIN 

  Delete FROM product where product_id= 104; 

EXCEPTION 

   WHEN Child_rec_exception THEN
      Dbms_output.put_line('Child records are present for this product_id.'); 

END;