Previous | Table of Contents | Next

Page 227

Avoiding an Infinite Loop in the Exception HandlerSometimes, you perform an action inside an exception handler that might itself raise an exception. This can lead to an infinite loop! The trick to avoiding this scenario is to enclose the exception handler in a block and give it its own exception handler. This embedded exception handler need not (nor should it) do anything. Consult the following:

EXCEPTION  -- main block handler
  WHEN OTHERS THEN
    BEGIN  -- embedded block
      IF (get_cursor%ISOPEN) THEN  -- cursor left open
        CLOSE get_cursor;  -- close it
      END IF;  -- cursor left open
    EXCEPTION
      WHEN OTHERS THEN
        NULL;  -- don't care
    END;  -- embedded block
END;  -- main block

Your exception handler should perform all necessary cleanup, such as closing cursors that might have been open.

TIP
Always put an exception handler in the topmost block of any application PL/SQL code. Exceptions should be handled in a graceful manner. The last thing a user needs to see is a terse, technical error message.

Defining Your Own ExceptionsIf the list of predefined exceptions seems a little short, you can define more of them by associating a name for any particular Oracle exception number. You can then reference the exception by name in the exception block. To accomplish this, define an exception and a pragma to convey to the compiler that you want to associate the exception with the given error code. A pragma is a compiler directive that is processed at compile time instead of runtime. It specifies how the compiler should handle language-specific information or conditions. All good compilers have them (or something like them). Listing 10.24 is an example of using a pragma.

Listing 10.24 excpinit.sql—Using EXCEPTION_INIT

SET SERVEROUTPUT ON

DECLARE
  -- exceptions and pragmas (compiler directives)
 INVALID_NUM_FORMAT EXCEPTION;  -- first we define an exception object
 PRAGMA EXCEPTION_INIT (INVALID_NUM_FORMAT, -1481);  -- then associate it
with Âan exception
  -- constants
  NUM_FMT CONSTANT VARCHAR2(3) := `aaa';  -- an invalid number format
  x NUMBER(10);
BEGIN
  DBMS_OUTPUT.enable;
  SELECT TO_NUMBER(`999', NUM_FMT) INTO x FROM DUAL;  -- try to convert
                                                          continues

Page 228

Listing 10.24 Continued

EXCEPTION
WHEN INVALID_NUM_FORMAT THEN
  DBMS_OUTPUT.put_line(`Trapped an illegal Number Conversion');
WHEN OTHERS THEN
  DBMS_OUTPUT.put_line(`Some other error');
END;
/

This time you get:

Trapped an illegal Number Conversion
PL/SQL procedure successfully completed.

Here you see a two-step process; you first define an exception, and then associate an Oracle error code with it. Your next question is, why didn't Oracle pre-define all their error codes? All 24,000 of them? As Richard Bach once wrote, "If you ask the question correctly, it answers itself." Oracle defined just the errors that give you 80% of the problems. After all, these things do take up space in memory.

Another method is defining application-specific exceptions. These have nothing to do with Oracle error codes. These are errors you define for your application. You have to detect a situation that violates a particular business rule, and raise the appropriate user-defined exception. For instance, suppose non-exempt employees who make less than $10 an hour are only allowed raises of 1 to 5 percent. Your buddy, Joe, is an hourly employee making $8 an hour. His boss (who's also his uncle) wants to give him a big raise. Listing 10.25 shows an implementation of this business rule.

Listing 10.25 busrule.sql—Trapping Business Rule Violations with Application-Specific Exceptions

DECLARE
  -- exceptions
  INVALID_ANNUAL_RAISE EXCEPTION;
  -- constants
  HRLY_STATUS CONSTANT VARCHAR2(1)   := `H';    -- hourly status
  HRLY_WAGE_LIMIT CONSTANT REAL(4,2) := 10.00;  -- upper hourly wage limit
  MIN_HRLY_RAISE CONSTANT REAL(5,4)  := 1.0100; -- lower hourly raise limit
  MAX_HRLY_RAISE CONSTANT REAL(5,4)  := 1.0500; -- upper hourly raise limit
  -- info entered by user
  emp_name   VARCHAR2(20) := `&emp_name';       -- employee name
  emp_status VARCHAR2(1)  := `&emp_status';     -- employee status
  hrly_wage  REAL(4,2)    := &wage;             -- hourly wage
  ann_raise  REAL(5,4)    := &annual_raise;     -- annual raise
BEGIN
  IF (emp_status = HRLY_STATUS AND              -- this is business rule #532
     hrly_wage < HRLY_WAGE_LIMIT AND

Page 229

     ann_raise NOT BETWEEN MIN_HRLY_RAISE AND MAX_HRLY_RAISE) THEN
    RAISE INVALID_ANNUAL_RAISE;
  ELSIF (FALSE) THEN  -- code other rules here (pertaining to raises)
    NULL;
  ELSE  -- passed the gauntlet
    DBMS_OUTPUT.put_line(emp_name || ` now makes ` ||
                         TO_CHAR(hrly_wage * ann_raise, `$99.99'));
  END IF;
EXCEPTION
WHEN INVALID_ANNUAL_RAISE THEN
  DBMS_OUTPUT.put_line(`Don''t give ` || emp_name || ` the raise!');
WHEN OTHERS THEN
  DBMS_OUTPUT.put_line(`Some other problem computing ` || emp_name ||
`''s increase');
END;
/

The server processes Joe's boss' request in this manner:

Enter value for emp_name: Joe
old  10:   emp_name   VARCHAR2(20) := `&emp_name';  -- employee name
new  10:   emp_name   VARCHAR2(20) := `Joe';        -- employee name
Enter value for emp_status: H
old  11:   emp_status VARCHAR2(1)  := `&emp_status'; -- employee status
new  11:   emp_status VARCHAR2(1)  := `H'; -- employment status
Enter value for wage: 8.00
old  12:   hrly_wage  REAL(4,2)    := &wage;            -- hourly wage
new  12:   hrly_wage  REAL(4,2)    := 8.00;             -- hourly wage
Enter value for annual_raise: 1.06
old  13:   ann_raise  REAL(5,4)    := &annual_raise;     -- annual raise
new  13:   ann_raise  REAL(5,4)    := 1.06;     -- annual raise
Don't give Joe the raise!
PL/SQL procedure successfully completed.

Using application-specific exceptions and raising them in your logic is a basic business rule encoding technique. Design your code for expansion and ease of maintenance by setting up a pattern for everyone to follow.

If you want the same code executed for two or more exceptions, just list them in a Boolean OR condition, such as:

EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
  …
WHEN OTHERS THEN
  …
END;

Handling Exceptions InlineA very good habit to get into is to code blocks with exception handlers for all SQL statements. If an exception occurs on the SQL statement, you usually want to continue processing inline (sequentially with the next conceptual program block). This is

Previous | Table of Contents | Next