web analytics
Oracle PL/SQL Exceptions Handling Options
codeling
Posted: Wednesday, October 12, 2016 10:01:25 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 919
Points: 3861

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
Sponsor
Posted: Wednesday, October 12, 2016 10:01:25 AM
codeling
Posted: Wednesday, October 12, 2016 10:04:59 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 919
Points: 3861

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

codeling
Posted: Wednesday, October 12, 2016 10:17:28 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 919
Points: 3861

How to Continue the Execution after an Exception Is Raised In Oracle PL/SQL

An exception handler lets you recover from an otherwise fatal error before exiting a block. But when the handler completes, the block is terminated. You cannot return to the current block from an exception handler. In the following example, if the SELECT INTO statement raises ZERO_DIVIDE, you cannot resume with the INSERT statement:

DECLARE
   pe_ratio NUMBER(3,1);
BEGIN
   DELETE FROM stats WHERE symbol = 'XYZ';
   SELECT price / NVL(earnings, 0) INTO pe_ratio FROM stocks
      WHERE symbol = 'XYZ';
   INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      NULL;
END;
/

You can still handle an exception for a statement, then continue with the next statement. Place the statement in its own sub-block with its own exception handlers. If an error occurs in the sub-block, a local handler can catch the exception. When the sub-block ends, the enclosing block continues to execute at the point where the sub-block ends. Consider the following example:

DECLARE
   pe_ratio NUMBER(3,1);
BEGIN
   DELETE FROM stats WHERE symbol = 'XYZ';
   BEGIN  ---------- sub-block begins
      SELECT price / NVL(earnings, 0) INTO pe_ratio FROM stocks
         WHERE symbol = 'XYZ';
   EXCEPTION
      WHEN ZERO_DIVIDE THEN
         pe_ratio := 0;
   END;  ---------- sub-block ends
   INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
EXCEPTION
   WHEN OTHERS THEN
      NULL;
END;
/

In this example, if the SELECT INTO statement raises a ZERO_DIVIDE exception, the local handler catches it and sets pe_ratio to zero. Execution of the handler is complete, so the sub-block terminates, and execution continues with the INSERT statement.

codeling
Posted: Wednesday, October 12, 2016 10:28:05 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 919
Points: 3861

Handling the Unnamed System Exceptions

Beside the above predefined system exceptions, those system exception 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;
Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2017 Digcode.com. All rights reserved.