Advanced Oracle Pl/Sql: Programming With Packages (Nutshell Handbook)
ISBN: B00006AVR6
EAN: N/A
EAN: N/A
Year: 1995
Pages: 195
Pages: 195
Authors: Steven Feuerstein, Debby Russell
| Chapter 22 Exception Handling |
Now that you have seen how to use the PLVexc plug-and-play component, let's go through the steps I followed to build this package. I will even show you two different stages of development of the package. It is very important for you to understand that while it is important to do it right the first time, it is also very unlikely that you will really and truly get it all correct in that first implementation. I present in this book software that I have massaged and fine-tuned repeatedly over the course of a year. You don't see all the pain, indecision, and transitional bugs of PL/Vision, but -- believe me -- they were there.
I learned in the course of building PLVexc that I need to progress through several phases in order to properly design and implement a package component. These phases are:
The first step I took to construct PLVexc was to understand fully the challenges faced by developers when building exception handlers in their PL/SQL programs. I have found that the more thoroughly I grasp the problem, the more accurate I will be in providing a solution.
What are all the nuances of exception handling in PL/SQL? What are the gotchas, the no-can-dos? What things are possible, but never before attempted?
Admit to yourself right up front: this is Version 1.0. It will change radically before you are done. But put something together so you can move on to the next phase.
The only way you can really tell if your approach is sound is to try it out. You will encounter requirements, interface issues, and restrictions you would never have discovered from an abstract or theoretical analysis.
Be prepared to go back and recode your package. You might even throw away the entire implementation and start again. It is far too easy to believe that just because you spent a lot of time writing and debugging your code and it sure looks pretty that it must be good. I try to never think about the hours of coding I have "thrown away." Too painful. Just accept it as part of the process. Be committed to constantly improving and you will eventually end up with something that you like -- and maybe a few others will find useful as well.
Let's look at how I applied these phases as I constructed the PLVexc package.
A developer must ask and answer many questions in order to properly bullet-proof an application from the perspective of handling errors. These questions include the following:
Which exceptions should I trap and handle? How do I handle the different kinds of exceptions that occur in my program?
When should I include a WHEN OTHERS handler? Do I have one at every level or just at the very top or outer block of my program?
When do I need to use the EXCEPTION_INIT pragma to associate error numbers with messages? How can I avoid using the same error number (in the -20NNN range) in different programs across my application? How can I guarantee a consistent set of messages for these errors?
When should I call RAISE_APPLICATION_ERROR rather than simply the RAISE statement to raise an exception?
When I handle an exception, what kind of action should I take? Do I ignore the error and continue, record that the error occurred and then continue, notify the error of the problem, stop execution of the program, reraise the same exception? Will the answer to this question change over time or from program to program?
If I record my errors, do I write the information to a database table or some memory-based structure, such as a PL/SQL table? What information should I include in the error text? Is all the data I want available from within PL/SQL (consider, for example, how you might determine the name of the program in which an error occurred)? What information will I need in order to do restores and fixes of the data?
If I write error log records to a database table, how do I make sure that the log is saved, but that the transaction I was working on in my application is rolled back (as necessitated by the error)? Remember that if an exception is passed unhandled out of a PL/SQL block, then all changes to PL/SQL data structures and database tables are rolled back implicitly and automatically.
Will I use the same log table for all of my applications, or should I provide a different log table for each application? Will I then have to create a different program to handle each log table? What should be the structure of the log table?
How do I write my exception handlers so that when I am in test and debug mode the information is available in one form (such as displayed on the screen), but when the code goes into production that information is redirected? And how, most importantly, can I do this without having to change my code right before it goes into production (which, at least theoretically, would require another round of tests)?
As you can see, it is very easy to ask lots of questions about exception handling. Some questions yield easy answers. Others require advance planning in application design. Still others are too basic to be answered by any generic package. Before I explore what a package can do for us in this situation, let's first examine the usual solution to these questions and the general perspective taken on exception handling.
The traditional approach taken by a PL/SQL based-development effort goes something like this:
Step 1. Train developers on how to write exception handlers. The training is basic and does not make any effort to teach programmers "best practices." Students and project managers alike are satisfied to have been at least introduced to the technology. And, of course, there isn't time for more advanced training. These people have an application to write!
Step 2. Develop some basic set of guidelines for displaying and recording errors in the application. In the more sophisticated and experienced organizations, the best developer might actually design a single log table for everyone to use.
Step 3. Distribute the guideline as little more than a glorified memo. Plead with or demand that the team members follow the standards as laid out in the document. Given the state of PL/SQL development and code management utilities, there really isn't any way to enforce the standards.
Step 4. Unleash the developers. Already behind in the schedule, there is a mad scramble to write the programs. The guidelines are followed, at best, unevenly. It is difficult to find time to remember what is written in a document and even more challenging to find time to write code as suggested. The main thing now is to simply churn out code without obvious points of failure.
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;
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:
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.
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.
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!
Let's now examine how a package-based solution can help overcome some of the weaknesses identified in the previous exception sections (the version of PLVexc I will be discussing and presenting may be found in the file PLVexc1.spp on the companion disk).
Before I dive into developing such a package, it is very important to come up with a set of objectives for the package, as well as an acknowledgment of those issues that the package does not address. My objective for the PLVexc package when I first attempted such a component was to make it easier for developers to handle exceptions in a consistent manner. Most importantly, this meant hiding the implementation of such complexities as writing to a log table, displaying errors to the end user or to the developer (during test mode). In addition, I wanted to provide a mechanism by which individual developers would have to deal with the EXCEPTION_INIT pragma and the -20NNN errors.
Specifically, I envisioned a mode of operation in which a developer could handle the NO_DATA_FOUND exception as shown in the previous example with this kind of code:
WHEN NO_DATA_FOUND THEN PLVexc.handle ('calc_totals', SQLCODE, PLVexc.c_recNstop, SQLERRM); END;With PLVexc, the developer calls the generic handle program to handle the exception. Arguments passed to handle include the name of the program in which the error occurred, the error code, the type of action to be performed (record and then halt). The fourth argument is the error text, in this case the text returned by SQLERRM.
The handle program offers the first glimmer of a declarative approach to exception handling. I ask PLVexc to handle my error. I pass it the necessary information, including a description of the type of action I want taken. I leave it to PLVexc to figure out how best to satisfy my request.
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.
PROCEDURE handle (context_in IN VARCHAR2, err_code_in IN INTEGER, handle_action_in IN VARCHAR2, string_in IN VARCHAR2 := SQLERRM) IS BEGIN set_context (context_in, err_code_in, string_in); IF recording_exception (handle_action_in) THEN record_exception; END IF; IF showing THEN display_exception; END IF; raise_exception (handle_action_in); END;
Well, not all that much later. Let's take a look now at each of the modules called within handle. We have:
|
|
|
|
|
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.
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:
Has the user passed a record action in the call to handle?
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.
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.
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.
PROCEDURE raise_exception (handle_action_in IN VARCHAR2) IS BEGIN IF handle_action_in = c_go OR handle_action_in = c_recNgo THEN NULL; ELSIF handle_action_in = c_stop OR handle_action_in = c_recNstop THEN IF curr_err_code BETWEEN min_err_code AND max_err_code THEN RAISE_APPLICATION_ERROR (curr_err_code, curr_err_info); ELSE RAISE process_halted; END IF; END IF; END;
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:
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.
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.
Rather than proceed step-by-step through the implementation of the current version of PLVexc (which is included on the companion disk), I will discuss the ways in which I used other packages of PL/Vision to enhance PLVexc. I will then examine the impact of these changes in specific areas of PLVexc.
I found (as explored in previous sections) that PLVexc actually combined functionality from (what should have been) several different packages. The "final" version of PLVexc makes use of the following packages:
Instead of hard-coding the text for messages with a call to SQLERRM or displaying the passed-in string, PLVexc calls the PLVmsg.text function. This program offers a much more flexible and centralized means of storing and providing message text.
The PL/Vision logging package is used both to perform rollbacks, if necessary, and to write error information to the log.
PLVtab provides the table used to maintain the list of bailout errors.
The PLVprs.display_wrap program replaces a call to DBMS_OUTPUT.PUT_LINE with a much more interesting and powerful paragraph-wrapping mechanism. You can now view long lines of error text with ease.
The trace package is used to maintain an execution stack. This stack can be used to automatically provide the name of the current program, avoiding the need to hard-code this value in the call to the exception handler.
By plugging all of the appropriate elements of PL/Vision into the PLVexc environment, I was able to change that package rapidly into a much more powerful and flexible component. Let's look at how I used PLVlog to greatly enhance the logging capability of PLVexc.
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.
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.
| 22.2 Application-Specific Exception Packages | VI. Testing Your Knowledge |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.