22.3.2.1 The traditional jumble of exception handling

With this traditional solution to exception handling, you end up with exception handlers that look like the following:

WHEN NO_DATA_FOUND
THEN
  DBMS_OUTPUT.PUT_LINE 
    ('Calc Totals Failed for ' ||
     v_company_name || ' at ' ||
     v_address1);
  INSERT INTO log_table
    (errcod, errmsg, progname, err_ts)
  VALUES
    (SQLCODE, SQLERRM, 'calc_totals', SYSDATE);
  RAISE;
END;

or this:

WHEN OTHERS
THEN
   /* Transaction has failed. */
   ROLLBACK TO trans_start;
   IF SQLCODE = -942
   THEN
      INSERT INTO log_table
        (errcod, errmsg, progname, err_ts)
      VALUES
        (SQLCODE, 'Company table not available', 'add_company', 
         SYSDATE);
   ELSE
      INSERT INTO log_table
        (errcod, errmsg, progname, err_ts)
      VALUES
        (SQLCODE, TO_CHAR (v_company_id), 'add_company', 
         SYSDATE);
   END IF;
   COMMIT;
END;

22.3.2.2 Drawbacks of the usual handlers

Let's examine these handlers in more detail. In the first example, I display a message to the user, write a record to the log table, and then reraise the same exception (NO_DATA_FOUND). What are the problems with this handler? I can think of three drawbacks:

1. Reliance on DBMS_OUTPUT. The hard-coding of the call to DBMS_OUTPUT.PUT_LINE procedure means that this handler sends the message to the screen only if output has been enabled and the host environment recognizes and supports DMBS_OUTPUT. This handler would not, for example, display anything in an Oracle Forms application. The error message, furthermore, will always be displayed when output is enabled. There is no flexibility available when using DBMS_OUTPUT. Finally, the string resulting from the concatenation might be longer than 255 characters, in which case PUT_LINE itself raises the VALUE_ERROR exception.

2. Exposure of log table implementation. The structure of the log table is hard-coded into the exception handler. By exposing this level of implementational detail, the programmer makes it very difficult to change or enhance the way information is sent to the log table. This approach also requires every developer to know the structure of the table, from the name of the table and its columns to the type of data passed to each column. Finally, this code assumes that no rollback of a transaction is required and that none will be issued in the future, since such a rollback would also wipe out the insert to the log table.

3. Experienced developers needed. On the one hand, you could argue that the code in that first example is simple enough. On the other hand, you could point out that a developer needs to know about the DBMS_OUTPUT builtin package, the SQLCODE and SQLERRM builtin functions, and the fact that the RAISE statement issued without any exception will reraise the same exception.[ 1 ]

   [1] I will make a confession: at the time that I wrote my first book, Oracle PL/SQL Programming , I was not aware of this special form of the RAISE statement.

There was more to that block of code than you might have thought at first! Now let's analyze the second example of the traditional exception handler. In this case, the developer was aware of transaction-related issues. The first step performed is a rollback to the savepoint issued at the start of the transaction. An IF statement then checks for a specific error that occurs when the specified table does not exist. The error information is then inserted in the log table. Immediately after the insert, the developer issues a COMMIT so that the log record is preserved.

While it is admirable that the developer thought of these issues, the handler still hard-codes and exposes the log table implementation. In addition, it is simply not always possible to issue a COMMIT inside an exception handler. That code assumes that any DML changes made prior to when the savepoint for trans_start was issued should be committed. I hope that is the case. More fundamentally, though, it is not at all clear to me that you want every developer in a project writing code that takes such liberty with the transaction integrity. Hard-coding specific errors like the -942 is also very questionable. Just consider how much time was spent (lost?) in writing that code: find the error number for the problem, write the code, test the different cases.

And of course these two examples would represent just a small percentage of all the code written in any application of substantial complexity to handle the full range of errors. There must be a better way to handle exceptions!

22.3.3.1 Implementing the generic handler

I relied heavily on top-down design techniques when implementing the handle program. Here is the logical flow for handle :

• If I am recording the exception, write the information to the table.

• If I am displaying the exception, show that information on the screen.

• Finally, if the developer has requested a halting action, raise an exception.

Example 22.2 shows the conversion of these requirements into PL/SQL code. As you can see, the handle program is very short because it relies heavily on private modules. As a result, the code is very readable. For example, after the call to set_context (explained below), I can literally read my program as follows: "If I am recording the exception, record the exception. If I am displaying errors, display the exception." and so on. No comments are needed. And, best of all, I don't yet have to know how I am going to implement the different programs. I have deferred that level of detail to a later time.

Well, not all that much later. Let's take a look now at each of the modules called within handle . We have:

22.3.3.2 Setting the context

The set_context procedure is a private module; it does not appear in the package specification. This procedure copies the arguments provided in the call to handle to "current exception" private variables, declared as follows:

/* Current exception information. */
curr_err_code PLVexc_log.code%TYPE;
curr_context VARCHAR2(100);
curr_err_info PLVexc_log.text%TYPE;

The PLVexc_log table is the structure used to hold the log of errors written from the PLVexc package.

I then reference these package variables in all of my other private modules that handle calls. By taking this approach, I avoid having to pass these values over and over again as parameters to these programs. You might be tempted to argue that I am writing less structured code since I am switching from parameters to package "globals." That would be true if all of my code and variables were not contained inside a single package. Package-level data such as curr_err_code does function like global data, but since it is declared in the body of the package I have control over references to and values in that data. Consequently, I do not incur the same risk of side effects as one usually does with nonparameterized references to global data.

Once I have set the context variables, I can move on to the substance of handle . Next task: record the exception -- if the user has requested this action.

22.3.3.3 Recording the exception

I created a Boolean function to return TRUE if the handle action is a record action, FALSE otherwise, shown below:

FUNCTION 





recording_exception 
   (handle_action_in IN VARCHAR2)
   RETURN BOOLEAN
IS
BEGIN
   RETURN UPPER (handle_action_in) LIKE 'R%' AND
          logging;
END;

There isn't much to this function; it is designed to encapsulate the logic by which I decide if the current exception should be logged. There are two parts to this decision:

1. Has the user passed a record action in the call to handle ?

2. Has the user turned on logging?

As you can see from the code for recording_exception , a record action is one that starts with R. The c_recNgo constant is set to RC. The c_recNstop constant is set to RH. Notice that this rule is not stated or applied anywhere outside of the recording_exception function. It is a level of detail that would be hard (and a waste of brain cells) to remember.

The other aspect to returning TRUE from this function is the call to logging. We now see the toggle coming into use. If the user has called the nolog procedure to turn off logging, the logging function returns FALSE, which means that recording_exception will return FALSE, which means that the record_exception program will not execute. Notice that even inside the body of PLVexc I do not make a direct reference to the log_flag variable. Instead, I always work through the programmatic interface.

If recording_exception does return TRUE, I call the record_exception procedure. This program encapsulates or hides the details involved in writing to the log table and is shown below:

PROCEDURE record_exception
IS
BEGIN
   INSERT INTO PLVexc_log
      (context, code, text, create_ts)
   VALUES
      (curr_context, curr_err_code, 
       curr_err_info, SYSDATE);
EXCEPTION
   WHEN OTHERS THEN NULL;
END;

The procedure is nothing more than an INSERT statement based on the current error variables assigned in the set_context program.

22.3.3.4 Displaying the exception

Once the error is recorded (or ignored), I display the exception if so requested. In this case, I check the value returned by showing (see "plsql-adv-ex-22-2"; this is the second application of the toggle inside the package). If TRUE, I call the display_exception procedure, which deals with all the niceties of constructing a string for display with DBMS_OUTPUT.PUT_LINE:

PROCEDURE display_exception IS
BEGIN
   DBMS_OUTPUT.PUT_LINE
      (curr_context || ' Code ' || 
       TO_CHAR (curr_err_code));

   DBMS_OUTPUT.PUT_LINE 
      (SUBSTR (curr_err_info, 1, 255));
END;

Notice that I perform a SUBSTR on the error information before I pass it to the DBMS_OUTPUT.PUT_LINE procedure. A longer string causes a VALUE_ERROR exception; that is just one of the complications when using the builtin package directly. In version 2 of PLVexc, I rely instead on PLVprs.display_wrap to automatically wrap the longer strings into a paragraph format.

22.3.3.5 Responding to the exception action

When I have finished with recording and displaying errors, as directed by the user, it is time to respond to the handle action. I do this with the raise_exception procedure, the last line in the handle body, shown in full in Example 22.3 . In this case, I have moved all the conditional logic ("should I raise an exception and how?") to the body of the private module. First, I check to see if the action (the only argument to raise_exception ) is a "continue action" ( c_go or c_recNgo ). If so, I do nothing by executing NULL -- I do not want to raise an exception if the developer has asked that the process simply continue.

On the other hand, if the developer has specified a "halt action" ( c_stop or c_recNstop ), I get to perform an additional service for the user of PLVexc: if the error code is between -20,000 and -20,999 (notice the BETWEEN statement that references the minimum and maximum error code constants), then raise_exception automatically makes use of the RAISE_APPLICATION_ERROR builtin to raise the exception. Otherwise, the normal RAISE statement is used with the special process_halted exception. In either case, an exception is propagated out of PLVexc, causing the enclosing block to halt, as was requested.

This feature of PLVexc transfers the burden of having to figure out "which way to raise" from individual developers (some of whom may be novices who are not even aware of RAISE_APPLICATION_ERROR) to the package. If, as I discuss below, you create an application-specific error handling package that contains predefined error numbers and exceptions for codes in the -20NNN range, a developer may not even know that she has raised an error that requires the RAISE_APPLICATION_ERROR builtin. Since PLVexc automatically determines the appropriate raise mechanism, that is one more complexity of which developers can remain blissfully ignorant.

Notice that no single program in PLVexc is more than 20 lines in length. Complex steps and expressions are separated and encapsulated behind named elements (variables, procedures, and functions). The result is a package body that looks simplistic, and one which (I hope) makes my readers say to themselves: "Jeez, I can do that !" It is true; you can build packages like this. First, you just need to adopt an impulse to your work that generates the initial ideas (roughly: I can and will improve my environment). Second, you must be fanatically devoted to writing tight, modular code.

22.3.3.6 Finding new uses for PLVexc

PLVexc Version 1.0 is so easy to use and so useful that I find myself applying it to other purposes as well. Two realizations ushered in this new phase:

1. PLVexc provides a mechanism for writing a message to a table. This could be used for auditing and logging of any information, not simply errors.

2. I don't have to call PLVexc.handle from within an exception handler. I could call it any time I want to write information out to a table for future analysis.

Suppose that I want to trace the progress of a data transformation and migration job. I find that I can use PLVexc.handle to write a message out to a table after every 100 transactions:

FOR upc_rec IN upc_cur
LOOP
   transform_upc (upc_rec.upc);
   migrate_upc (upc_rec.upc);
   IF MOD (upc_cur%ROWCOUNT, 100) = 0
   THEN
      PLVexc.handle
         ('transmigr', 0, 
          PLVexc.c_recNgo,
          upc_rec.upc, upc_rec.description);
   END IF;
END LOOP;

This works wonderfully, and can be seen as a validation of the architecture of the PLVexc package. Difficulty arises, however, when I want to examine the contents of the exception log. Since I am now using it for multiple purposes, I have to distinguish between error records and trace records. In addition, I discover that I may want to record my errors without also recording my trace. Since I am using the same package with the same toggles, however, it's all or nothing -- never a good situation for a developer.

I also become uncomfortable with using an exception handler package as a trace mechanism. It's very convenient, but it is also, strictly speaking, outside of the scope of the original package. That fact should set a red flag waving before your eyes. This adaptation of PLVexc is akin to raising an exception to perform a conditional branching in your program. You can make it work, but all it really does is sow confusion and create maintenance/enhancement nightmares.

When you find yourself using programs in ways different from those originally intended, you should perform some analysis and figure out if this use is justified. If not, change your code to a more straightforward implementation.

In the case of PLVexc, such an analysis yields the realization that I am much better off separating my logging code from my exception handling code. Logging of errors is just one special case of all my logging needs. Tracing code execution is another special case. I am able to adapt PLVexc to meet my other needs, but a better long-term solution would be to create a logging package that can be called by PLVexc and by my trace programs.

And so, after my lengthy development cycle for PLVexc, I come to the conclusion that I should rework the internals of the package. Should I be depressed by this development? Not at all. I have learned, over my years of PL/SQL development, that while I should make every effort to do it right the first time, it is also absolutely impossible to get it right the first time. "Right" is a moving target based more on philosophy than on requirements.

As long as I apply my guidelines for best practices at each stage of development, I am certain that my programs will be both immediately useful and easily enhanced to meet future needs. This first version of PLVexc offers a significant improvement over business-as-usual methods for exception handling. That fact does not stop me, however, from crafting yet another version of PLVexc that is much more powerful and easy to use.

22.3.4.1 Leveraging PLVLog

In the first version of PLVexc, my local record_exception procedure contained a hard-coded INSERT statement to a specific table. In the PL/Vision version of PLVexc, I replace that INSERT statement -- indeed, that entire local module -- with a call to PLVlog as shown below:

IF recording_exception (handle_action_in)
THEN
   PLVlog.put_line 
      (context_in, err_code_in, msg_in, USER,
       rb, TRUE);
END IF;

where rb is the function that returns the current "rollback to" behavior for exception handling and TRUE indicates that this call to put_line should override the current logging status. This means that even if logging is turned off outside of PLVexc, you will still be able to log your error information.

With this simple substitution, I have accomplished the following:

• Disconnected the exception-handling component from a logging capability. If you want to log information that is not an error, you can do so directly with the PLVlog package. You do not have to tweak PLVexc to coincidentally provide this functionality.

• Greatly increased the logging capability of PLVexc. Since I am now using PLVlog, my exception logging is no longer restricted to simply writing to a database table. I can log to my choice of database table, PL/SQL table, standard output, and string. And as new options appear and are integrated into PLVlog, those options become instantly available to PLVexc as well.

A similar kind of improvement is achieved for showing the error (turned on with a call to PLVexc.show ). Instead of simply calling DBMS_OUTPUT.PUT_LINE in my display_exception program, I can instead call the PLVprs.display_wrap so that very long text messages can be displayed in full in wrapped format.

22.3.4.2 Implementing the high-level handlers

The most far-reaching and interesting aspects of the evolution of PLVexc are the high-level handlers. I find it satisfying to be able to type something as simple as:

PLVexc.

recNgo;

and have prebuilt code automatically determine the current program, error number, error message, and everything else I need to record that error and then continue.

You have already seen the headers for these high-level handlers and learned about the difficulties of tracking the current program in PL/SQL . Now let's go inside the PLVexc package to see how I implemented these handlers.

There are a total of eight high-level handlers, with overloadings for string and integer versions for each different action. As you can well imagine, the code that needs to be executed within each of these handlers is very similar. I found, in fact, that the only difference is the action requested and the input (message or error code). Consequently, I built a private procedure (it does not appear in the package specification) that is called by all the high-level handlers. I named this program terminate_and_handle for reasons that will be clear in a moment and called it in my recNgo handlers as follows:

PROCEDURE recNgo (msg_in IN VARCHAR2 := NULL)
IS
BEGIN
   terminate_and_handle (c_recNgo, msg_in);
END;

PROCEDURE recNgo (err_code_in IN INTEGER)
IS
BEGIN
   

terminate_and_handle (c_recNgo, err_code_in);
END;

A few things to notice: first, if you do not provide an argument when you call recNgo , PLVexc executes the string version, which means that it records the value returned by SQLCODE as the error. Second, I call terminate_and_handle in each of these two overloaded procedures -- so terminate_and_handle must also be overloaded. Here, in fact, is the body of the code behind the two versions of terminate_and_handle :

PROCEDURE terminate_and_handle
   (action_in IN VARCHAR2,
    msg_in IN VARCHAR2 := NULL)
IS
BEGIN
   PLVtrc.terminate;
   handle (PLVtrc.prevmod, SQLCODE, action_in, 
       NVL (msg_in, PLVmsg.text (SQLCODE)));
END;

PROCEDURE terminate_and_handle
   (action_in IN VARCHAR2, 
    err_code_in IN INTEGER)
IS
BEGIN
   PLVtrc.terminate;
   handle 
      (PLVtrc.prevmod, err_code_in, action_in, 
       PLVmsg.text (err_code_in)); 
END;

You can probably see how I came up with the name for this private procedure. It does only two things: first, it calls PLVtrc.terminate to remove the current program from the PLVtrc-managed execution stack. Second, it calls that good old, low-level handler program to handle the error according to the information provided to it from various sources. It obtains the context from the PLVtrc.prevmod function (this returns the previous module, since the call to PLVtrc.terminate has already popped the execution call stack). This convoluted approach was necessary because I couldn't wait to do the terminate after the call to handle. That program might raise an exception!

If I have called the string version of a high-level handler and, hence, the string version of terminate_and_handle , I rely on SQLCODE to retrieve the error, but then pass the input string as the error text (unless it is NULL). If I have called an integer version of the handler, that value is used as the error code. That same number is also passed to PLVmsg.text to retrieve the text for that error.

Again, by leveraging all of these other PL/Vision packages, I can actually simplify the code required in my handlers, but end up with a much more robust implementation.