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.sqlUsing 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.sqlTrapping 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