|
Chapter 22 |
|
|
|
Chapter 22 |
|
Contents:
The Challenge of Exception Handling
Application-Specific Exception Packages
Implementing PLVexc
The PLVexc (PL/Vision EXCeption handling) package provides a powerful, plug-and-play component to perform exception handling in your PL/SQL programs. It makes use of PLVlog to automatically write errors to the log of your choice (database table, PL/SQL table, etc.). It offers high-level exception handler programs so that individual developers can simply call a procedure that describes the desired action, such as "record and continue," and PLVexc figures out what to do.
This chapter first analyzes the need for exception handling and the traditional solutions in a PL/SQL environment. It then presents the elements of the PLVexc package, along with the information you need to apply this functionality in your own programs. Finally, the chapter explores the implementation of PLVexc in two phases.
Do you test your own programs? If you do, I am willing to wager large sums of money that your users feel as if they are the ones doing the testing. Authors of a program cannot find all the bugs in their software. They have, in fact, an uncanny ability to unconsciously follow a path through their code that avoids all bugs and logical holes.Programmers cannot be responsible for testing their own code.
Tightly linked to proper testing is proper error handling. Even if a program does not have actual bugs, it does need to handle abnormal conditions gracefully. Yet exception handling is the last thing any of us wants to worry about when we build our PL/SQL programs. To recognize the need to write exception handlers is to acknowledge that things might go wrong. Who can afford the time and resources to focus on the negative? It is all we can do to get our programs to work properly under normal circumstances -- to conform, in other words, to the specifications. How many times do you hear others (never yourself, right?) say things like this: "After I get my program to work, I'll go back and put in the exception handlers." Of course, no one ever has the time to "go back."
Anticipating and handling the problems in one's program is crucial to writing a robust application. PL/SQL offers a very powerful architecture for dealing with abnormal conditions: the exception section and exception handlers. Yet this architecture can also be difficult to use, particularly in a manner that ensures consistent error handling across an entire application. This difficulty, combined with the importance of getting it right, makes exception handling an excellent candidate for the use of a plug-and-play component.
A plug-and-play component, based in PL/SQL package technology, can hide the complexities and difficulties of exception handling. It can provide a declarative interface to both responding to and logging problems encountered in an application. With this declarative approach, programmers can, in the exception section, state what they want the program to do and leave it to the underlying engine (the package) to figure out how to get the job done. This is very similar to the theory and practices of the SQL language.
To give you a taste of what is possible, the following exception section has two different exception handlers: one for NO_DATA_FOUND and one for all other exceptions. When NO_DATA_FOUND is raised, a message is displayed to the user, the error is recorded, and then the program is halted. When any other error occurs, that error is recorded and then processing continues.
EXCEPTION WHEN NO_DATA_FOUND THEN PLVexc.recNstop ('Company has not been located.'); WHEN OTHERS THEN PLVexc.recNgo; END;
Both of these exception handlers make use of the PLVexc package. This package automatically records the current program, user, error number, and message. It relies on the PLVlog package so that the recording can take place to a database table or to a PL/SQL table. It performs rollbacks to the last savepoint if requested so that the current transaction is "erased," but the write to the log table is preserved.
There is, in other words, a lot going on when a developer makes a simple, high-level call to a PLVexc error handling procedure. Yet all that activity is rendered invisible by the package. All the user of PLVexc has to know is what kind of action he wants to perform. Ignore the error? Record and continue? Record and halt? Just tell PLVexc what it is you want to do and let the component do the rest.
The PLVexc package is the best example in PL/Vision of a plug-and-play component, prebuilt code you can plug into your own programs, instantly improving the functionality, reliability, and flexibility of your own applications. In this chapter, I'll first show you how to use PLVexc. Then I'll present the implementation of PLVexc in two stages: the first version I built and the second, "final" version of the package you will find on the disk.
I show you the two stages of development and design of PLVexc so that you can follow the thought process I used to move from a specific problem to an increasingly generalized solution. Learning how to use PLVexc in your environment is important, but even more important and more difficult is to develop your own analytical and programming skills so that you can build your own plug-and-play package-based components.
The PLVexc package provides several predefined exceptions for you to use and also to provide a model for adding additional exceptions in the same way to PLVexc. These exceptions are:
ORA-00942: table or view does not exist
ORA-01555: snapshot too old (rollback segment too small)
A third exception, process_halted , is used in the bailout feature and is discussed later in this chapter.
The code required to declare these exceptions is as follows:
no_such_table EXCEPTION; PRAGMA EXCEPTION_INIT (no_such_table, -942); snapshot_too_old EXCEPTION; PRAGMA EXCEPTION_INIT (snapshot_too_old, -1555);
Why does PLVexc predefine these exceptions (and invite you to add more)? So every developer in your organization does not have to take these steps over and over again. Specifically, to achieve:
Consistent error handling in the application: developers will not define their own named exceptions willy-nilly throughout their programs. They make use of existing names for exceptions. Instead of coding their own handlers for WHEN OTHERS, they can use the prebuilt program, display_error .
Less buggy code: Novice and intermediate programmers do not have to deal with the EXCEPTION_INIT pragma and other complicated issues related to exception handlers. With the package, these details are shielded from the developer.
Once such exceptions are defined in a package, individual developers no longer need to know about the specific error numbers, nor do they have to be hassled with EXCEPTION_INIT syntax.
Compare the two PL/SQL blocks below. The first demonstrates the kind of code one would have to write without the predefined exceptions as found in PLVexc.
DECLARE no_such_table EXCEPTION; PRAGMA EXCEPTION_INIT (no_such_table, -942); BEGIN perform_action; EXCEPTION WHEN no_such_table THEN do_something; END;
The second block shows how one's code would look with PLVexc in place:
BEGIN perform_action; EXCEPTION WHEN PLVexc.no_such_table THEN PLVexc.handle ('action', SQLCODE, PLVexc.c_recNstop); END;
No declaration section required, no need for the developer to write all that extra code. Just pick from the package-based selections and keep on developing! The benefits of this approach go well beyond short-term productivity gains. If all developers work from this predefined set of exceptions, overall application code volume is reduced, and along with that the number of bugs that are introduced and then tested out of the application.
The PLVexc package contains only two predefined exceptions. When you apply this technique into your own environment you will undoubtedly want to add to this section any of the unnamed system exceptions your developers will encounter routinely. And even if you don't think of them all before development begins, you can always add to the set of exceptions as you proceed. Simply set as a guideline for programmers that they never use the EXCEPTION_INIT pragma in their code. Instead, they take the time to add that exception to the package and then reference the package-based exception.
One of the major improvements offered by PLVexc is the ability for a developer to simply state the kind of action needed in a particular exception handler. You can do this in two ways: pass the action as the third argument to the low-level handle procedure or call a high-level handler whose very name encapsulates the action.
PLVexc supports four different exception-handling actions. These are presented in the table below, along with the corresponding packaged constants used in the call to handle and the corresponding high-level handler program.
By providing named constants, users of PLVexc do not have to be aware of the specific literal values used by PLVexc. This advantage is even greater when calling the stop or recNgo programs. Instead of passing cryptic acronyms, users can rely on named elements to make their code self-documenting and easy to maintain. Most importantly, users do not have to be aware of either how the recording process takes place or how the program is halted. They can just make the request.
Another kind of action you might want to take is to reraise the same exception that brought you into the exception section. You can do this in PL/SQL by issuing an unqualified RAISE statement, as shown below:
EXCEPTION WHEN OTHERS THEN p.l ('Error code: ', SQLCODE); RAISE;
In this fragment, when any error occurs, I display the error code and then reraise that same error to propagate the exception out to the enclosing block.
This is a very useful feature of PL/SQL and it seemed only reasonable to implement this action in PLVexc when I encountered this functionality about six months ago (yet another aspect of PL/SQL of which I was ignorant when writing Oracle PL/SQL Programming ). Eager to please, I spent an hour or two adding the necessary constant, high-level handler and accompanying code. A simplified version of my high-level handler looked like this:
PROCEDURE reraise IS BEGIN p.l ('description of error'); RAISE; END;
Confident of my new approach, I even put together the following test script to test out my new functionality:
BEGIN p.l ('Divide by zero!', 1/0); EXCEPTION WHEN OTHERS THEN PLVrec.reraise; END;
Finally, after all of my furious coding, it was time to compile my PLVexc package and run some tests. Imagine my surprise when I received the following compile error on the reraise procedure:
PLS-00367: a RAISE statement with no exception name must be inside an exception
It turned out that my great idea was a completely invalid idea! You can only issue the unqualified RAISE statement inside an exception section, which makes a whole lot of sense. If you are not inside an exception section, there is no current exception, so the statement:
RAISE;
makes no sense at all. What are you raising? A roof? A stink? I sheepishly deleted all reraise -related code from my package. Some PL/SQL guru!
The reason I relate this story to you is that it taught me (well, reminded me of) an important lesson: before you embark on building powerful, generic utilities, do the research necessary to prove that your ideas are possible and practical.
Now that you are familiar with the different kind of actions you can request in PLVexc, let's look at the programs you can call to handle your errors. PLVexc provides two levels of handlers. The low-level program, the handle procedure, allows (and expects) you to fully specify all the information about the exception in the argument list. The high-level programs rely on a higher level of abstraction, making it easier to use the PLVexc package; PL/Vision automatically figures out the where, what, and when of the problem.
The header for the low-level, generic exception handler program is as follows:
PROCEDURE handle (context_in IN VARCHAR2, err_code_in IN INTEGER, handle_action_in IN VARCHAR2, string_in IN VARCHAR2 := SQLERRM);
The handle procedure accepts four arguments, which are explained below:
context_in
The context or program in which the error occurred. This is free-form text, but as I explain later in Section 22.2, "Application-Specific Exception Packages" , you want to use named constants to avoid chaos in this area.
err_code_in
The error code. You generally pass in the SQLCODE function for this argument, since that shows the current SQL layer error. You can, however, pass any integer, including application-specific errors in the -20NNN range as well.
handle_action_in
The string constant that informs PLVexc of the action you want to take, such as "record and continue" or "halt." The only values you should pass are the PLVexc constants: c_go , c_recNgo , c_stop , or c_recNstop .
string_in
The error message, the default of which is that string returned by the SQLERRM function.
If your error code falls within the range -20,000 to -20,999 and you request that the program be halted, then PLVexc automatically calls RAISE_APPLICATION_ERROR to raise the error and communicate the error information back to the client program. Otherwise, when you want the program halted, PLVexc.handle uses the RAISE statement to raise the process_halted exception defined in the package.
The various arguments allow developers to handle exceptions in different ways and pass very specific information to the exception-handling component. Let's look at some examples.
Write an exception handler to detect NO_DATA_FOUND, in which case information about the current record is saved and the application continues.
EXCEPTION WHEN NO_DATA_FOUND THEN PLVexc.handle ('getcompany', SQLCODE, PLVexc.c_recNgo, TO_CHAR (v_company_id)); END;
NOTE: Remember that logging or displaying must be turned on in order for a "record" request like that shown above to actually have an impact. See Section 22.1.7, " Recording Errors" for more information on these features.
Write an exception to detect a failure in the current program and, instead of relying on SQLCODE , pass a -20NNN error number with an accompanying message. This would be necessary if the error occurs inside a database trigger and must be passed back to the client application.
EXCEPTION WHEN OTHERS THEN PLVexc.handle ('check_emp_age', -20500, PLVexc.c_recNstop, 'Employee too young: ' || TO_CHAR (:new.birthdate)); END;
The PLVexc.handle is a significant improvement over writing your own exception-handling code over and over again. Still, it requires that you enter lots of information. You have to provide the current program, the error code, and the action (the error message is optional). Doesn't it seem silly that you would have to tell a PL/SQL program the name of the program that is currently executing? Shouldn't that information be accessible from PL/SQL itself? And shouldn't the PL/SQL runtime engine know about the current error?
The answer to all these questions is "yes and no." Yes, the PL/SQL runtime engine should know about the current program name and the current errors -- and in many circumstances it does know about this information. Unfortunately (and here's the "no" part), while the DBMS_UTILITY.FORMAT_CALL_STACK does return the active PL/SQL execution stack, it does not tell you which program inside a package is being executed (see Chapter 21, PLVlog and PLVtrc: Logging and Tracing , for more information on this phenomenon). And it is quite impossible for PL/SQL to know the current error when it is an application-specific problem (you raised a programmer-defined exception).
It is possible, on the other hand, to overcome these complications. Using the PL/Vision message, trace, and exception-handling packages in an integrated fashion, you can greatly simplify the task of providing comprehensive exception handling in your applications.
The high-level handler programs of PLVexc hide almost all the details and data needed to respond to and record exceptions. The headers for these handlers are:
PROCEDURE recNgo (msg_in IN VARCHAR2 := NULL); PROCEDURE recNgo (err_code_in IN INTEGER); PROCEDURE go (msg_in IN VARCHAR2 := NULL); PROCEDURE go (err_code_in IN INTEGER); PROCEDURE recNstop (msg_in IN VARCHAR2 := NULL); PROCEDURE recNstop (err_code_in IN INTEGER); PROCEDURE stop (msg_in IN VARCHAR2 := NULL); PROCEDURE stop (err_code_in IN INTEGER);
Notice that there is a handler name for each action and there are two versions for each action: one that accepts an additional message and one that has an integer argument. If you call a handler with a string, that string is recorded or displayed as the error message. If you pass a string to a high-level handler like recNstop , it will use the value returned by the SQLCODE function as the error number, and your string as the error message.
If you call a handler with an integer error code, the error message is retrieved from PLVmsg facility based on that numeric code (see Section 22.1.6.4, " Defining error messages with PLVmsg" ). And those are the only two types of information to pass to PLVexc.
What about the action code? With the high-level handlers, the action you want taken has been moved from the parameter list of the program to the very name of the program itself.
What about the current program name? You don't provide it in the call. Instead, you define the current program with a call to PLVtrc.startup at the beginning of each program unit (see Section 22.1.6.5, " Integrating PLVexc with PLVtrc " ). Can exception handling get any easier than that?
Let's look at some examples of using these handlers.
When my implicit query retrieves too many rows, I want to simply continue processing. For any other errors, record and halt.
EXCEPTION WHEN TOO_MANY_ROWS THEN PLVexc.go; WHEN OTHERS THEN PLVexc.recNstop;
When my database trigger on the emp table detects that the employee is underage, it raises a packaged exception in the -20NNN range. This error number is given a name in the empmaint package, which is then passed to the call to PLVexc.recNhalt so that this application-specific exception can be registered.
BEGIN IF :new.birthdate > ADD_MONTHS (SYSDATE, -216 /* 12 x 18 */) THEN RAISE empmaint.too_young; END IF;
EXCEPTION WHEN too_young
THEN PLVexc.recNhalt (empmaint.en_too_young); END;
When a duplicate value in an index exception is raised, store the current primary key and other unique information for the new company so that you can figure out what went wrong. Then continue with the processing.
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN PLVexc.recNgo (TO_CHAR (v_comp_id) || '-' || v_comp_nm)); END;
There are two scenarios under which PLVexc obtains the text of an error message from the PLVmsg package:
When you use an integer version of a high-level handler (you pass in an error number).
When you use the string version of a high-level handler, but pass in a NULL string (the default value).
In both of these cases, the handler calls PLVmsg.text , passing it either the value returned by SQLCODE or your own error number, in order to obtain the error text. If the error number is between -20,000 and -20,999, the PLVmsg package tries to get the error message from the text table maintained by the PLVmsg package. Let's look at how you would put all these pieces in place for the trigger discussed in the previous section. I passed the error number empmaint.en_too_young in my call to recNhalt . Suppose this constant is defined as:
en_too_young CONSTANT INTEGER := -20033;
in the empmaint package. Then in the initialization section of that package I should also execute the code necessary to store the message for this error in the PLVmsg table. I would do this by issuing a call to PLVmsg.add_text procedure as shown below:
PACKAGE BODY empmaint IS en_too_young CONSTANT INTEGER := -20033; ... all the code .. /* The initialization section */ BEGIN PLVmsg.add_text (en_too_young,
'Employee must be at least 18 years old.'); END;
In fact, for every error number defined in the package, I would need a call to PLVmsg.add_text to make that string accessible through the PLVmsg interface and, thus, to the PLVexc handler programs.
As noted in the previous section, when you use the high-level handler programs such as go and recNstop , you do not have to tell PLVexc the name of the current program. This is true, however, only if you integrate your use of PLVexc with the PLVtrc package.
PLVtrc provides two programs to build and maintain its own execution stack of PL/SQL programs. You call PLVtrc.startup to indicate to PL/Vision that a new program has started (and this program can be a procedure, function, or even anonymous block). You call PLVtrc.terminate to indicate to PL/Vision that the current program has ended. The body of the calc_totals procedure below demonstrates this approach:
PROCEDURE calc_totals IS BEGIN PLVtrc.startup ('ct'); ALL_OTHER_CODE; PLVtrc.terminate; EXCEPTION WHEN NO_DATA_FOUND THEN PLVexc.continue; WHEN balance_too_low THEN PLVexc.halt; WHEN OTHERS THEN PLVtrc.terminate; RAISE; END;
The first line in the body calls startup . Then all the rest of the code is executed. The last line in the procedure calls the trace terminate program. These statements manage the execution stack for successful execution of calc_totals . Now let's look at the exception handlers. In the first two handlers (for NO_DATA_FOUND and balance_too_low ), I call one of my high-level PLVexc handler programs. These programs automatically maintain the PLVtrc execution stack with a call to PLVexc.terminate , so you do not have to do it yourself. The last, OTHERS handler only reraises the exception. Since it does not use a PLVexc handler, I must include an explicit call to PLVexc.terminate to update the execution stack.
In the next example, I use the startup and terminate procedures to track execution of a nested, anonymous block.
PROCEDURE annual_calcs (val in number) IS BEGIN PLVtrc.startup ('proc'); calc_gross_revenue; BEGIN PLVtrc.startup ('analyze'); calc_rev_distribution; PLVtrc.terminate; EXCEPTION WHEN OTHERS THEN PLVexc.halt; END; call_profits; PLVtrc.terminate; EXCEPTION WHEN OTHERS THEN PLVexc.rec_continue; END; /
With this code you can see some of the additional power and flexibility available with PL/Vision. There is no way at all to track through the PL/SQL stack the startup and execution of a local, anonymous block. Sure, you have to code it yourself, but at least you can get the information you need. Furthermore, if you don't turn on the trace, the overhead incurred by the PLVexc program calls is minimal.
Recognizing the difficulty of even remembering to include calls to PLVtrc modules in your programs, the PL/Vision code generator package, PLVgen, generates procedures and functions with calls to startup and terminate already in place. So if you start to use PLVgen as a starting point for your program creation, you will be able to leverage all of these components of PL/Vision and actually be more productive.
As you can see from the exception-handling actions, you can record an error in PLVexc. This package gives you two options for how to record the process:
Write the error information to the PL/Vision log through calls to PLVlog.
Display the error information to the screen (or standard output) using the p.l procedure.
You can perform both of these steps simultaneously when an error occurs, or you can turn on only one of these options. The programs to manage these record features are discussed below.
PLVexc provides a standard PL/Vision toggle to control logging of errors with PLVlog. The headers for these programs are:
PROCEDURE log; PROCEDURE nolog; FUNCTION logging RETURN BOOLEAN;
These toggles allow developers to change the behavior of exception handling in PLVexc without making any changes to their application or to the PLVexc package itself. When you want to record the errors to the PL/Vision log, you simply execute this command before running the application:
PLVexc.log;
Then each time a PLVexc handler program is executed, the following information is written to the log:
Current program
Error code
Error message
User who raised the exception
Time/date of logging
The default value for logging in PLVexc is that it is turned on. You do not, in other words, have to call PLVexc.log to turn on logging if you have just started up your session.
PLVexc provides a standard PL/Vision toggle to control showing of errors with PLVshow. The headers for these programs are:
PROCEDURE show; PROCEDURE noshow; FUNCTION showing RETURN BOOLEAN;
These toggles allow developers to change the behavior of exception handling in PLVexc without making any changes to their application or to the PLVexc package itself. When you want to view the exceptions as they occur (or, at least, when the PL/SQL program completes its execution), you simply enter the following command:
PLVexc.show;
When you are done viewing the errors and only want the information logged (or completely ignored, depending on the value returned by PLVexc.logging ), you execute this command:
PLVexc.noshow;
The default value for showing exceptions from PLVexc is that it is turned off. You must, in other words, call PLVexc.show when you want to view exceptions directly from the screen.
Logging and showing of errors are completely independent actions. You do not have to have logging turned on in order to also show the errors.
The following scenarios will give you a better idea of when and how these toggles would be used.
Scenario 1: Suppose that I want to execute a batch procedure that transfers and transforms several million rows from a temporary table to its final resting place. I know in advance that the load process will generate thousands of exceptions. I know also that it is not necessary to keep track of these errors, so I do not want to clog up my log table with that information. In the following script, therefore, I turn off both logging and display of errors and then execute the batch load.
BEGIN PLVexc.nolog; PLVexc.noshow; batch_load (SYSDATE); END; /
Scenario 2: I am testing a new program and expect errors to pop up. Rather than go through the trouble of querying the contents of the log table, I would like to simply display errors to the screen and respond immediately.
SQL> exec PLVexc.nolog; SQL> exec PLVexc.show; SQL> exec new_program; proc1 Code -6502 ORA-06502: PL/SQL: numeric or value error
Of course, when the application moves to production status, you will want to log errors to a table or some kind of repository. You rarely ever want to display them directly to users. Consequently, these are the default settings for the PLVexc toggles.
PLVexc provides a PL/Vision toggle to control whether PLVexc requests a rollback (executed within PLVlog) before error information is written to the log. The headers for these programs are:
PROCEDURE rblast; PROCEDURE rbdef; PROCEDURE norb; FUNCTION rb RETURN VARCHAR2;
The default is to perform a rollback to the last savepoint set with a call to PLVrb.set_savepoint ( rblast ). If you do not want a rollback to occur before logging the error, issue this command before you start your application:
PLVexc.norb;
You have only three options concerning rollbacks from within PLVexc:
Roll back to the last savepoint set by calling PLVrb.set_savepoint . Call PLVexc.rblast to select this behavior (the default).
Perform a rollback based on the current/default behavior defined in PLVlog (see Chapter 21 ). Call PLVexc.rbdef to select this behavior.
Do not perform any rollback before insertion of error information into the PL/Vision log. Call PLVexc.norb to select this behavior (the default).
If you want to see if PLVexc is currently requesting a rollback, call the rb function. It returns the PLVlog rollback action code.
When you request that the processing in your application halt by calling recNstop or simply stop , the PLVexc takes all appropriate actions and then issues the following statement:
RAISE process_halted;
The process_halted exception is declared by name in the specification of the PLVexc package. It is not associated with any error number in the -20NNN range. As a result, there are only two ways you can trap this exception once it is raised.
With an exception handler specifically for this exception, which would look like this:
WHEN PLVexc.process_halted THEN do_something;
With a WHEN OTHERS section, which traps any and every kind of exception.
You can choose to handle the halting exception, in which case the processing of your application might still be able to continue. Generally, however, you will not trap this exception and instead let it propagate up to the top of the PL/SQL calling stack, where it goes unhandled. A WHEN OTHERS handler will, of course, trap this exception. As a final option, you might handle this exception at the outermost block so that you can perform a commit and save writes to the error log (if it is a database table).
Using the bailout feature, you can also truly and completely bail out of your program regardless of the presence of a WHEN OTHERS section. See Section 22.1.10, " Bailing Out with PLVexc" for more information on this feature.
You have already seen how you can stop your current program by requesting a halting action. This request causes PLVexc to raise an exception as follows:
RAISE process_halted;
This exception propagates out of, and closes, enclosing PL/SQL blocks until it hits an exception handler specifically for that exception or until it encounters a WHEN OTHERS exception.
But what if you really want to bail out entirely and immediately from your application? I encountered a situation recently where a very long-running program would work fine for an hour or two, but then raise an ORA-3113 error: "end of file on communication channel". My program had lost its connection; there was no point in going on. Yet because I had made rigorous use of my PLVexc exception handler programs, I trapped the error, logged the problem, and continued on to the next transaction. It made no sense, given the error, but on it went, accumulating error log records until the tablespace was full, at which point the application generated ORA-3113 and ORA-1547 errors. I had a full-scale mess on my hands.
This experience brought to light a different class of errors: fatal problems whose occurrence should always signal the need for a total shutdown of the application. There is, after all, little point in continuing when you are not connected to the database.
PLVexc supports this functionality by allowing you to specify a set of error codes which, when raised, cause an unstoppable "bail out" from your program. These are called the bailout errors . As long as you use PLVexc handlers in all of your exception sections, a bailout error will propagate out of WHEN OTHERS sections even if you specify a continue action.
There are two aspects to the bailout feature: (a) establishing the list of bailout errors; and (b) starting and stopping the bailout itself. It is up to the users of PLVexc to create a list of bailout errors. It is usually left to PLVexc to initiate the bailout; you can, however, do this yourself as well. The programs that support both parts of bailing out are covered below.
You add an error number to the bailout error list by calling the bailout_on procedure, whose header is shown below:
PROCEDURE bailout_on (err_code_in IN INTEGER);
The following statements add several error codes to the bailout error list. In a production environment, you might even place these calls inside a login.sql script which is run whenever SQL*Plus is initiated to run a regular, batch process.
PLVexc.bailout_on (-3113); /* end-of-file on comm error */ PLVexc.bailout_on (-1547); /* failed to allocate extent */ PLVexc.bailout_on (-1555); /* snapshot too old */ PLVexc.bailout_on (-1562); /* can't extend rollback */
To remove an error number from the bailout error list, call the nobailout_on procedure:
PROCEDURE nobailout_on (err_code_in IN INTEGER);
To clear the entire list of bailout errors, call the clear_bailouts procedure:
PROCEDURE clear_bailouts;
To determine if an error number is currently on the bailout list, call the bailout_error function, whose header is shown below:
FUNCTION bailout_error (err_code_in IN INTEGER) RETURN BOOLEAN;
Once you have defined your bailout list, you (and, more to the point, PLVexc) can reference this list to determine if a bailout should be initiated or stopped.
The bailout procedure raises the process_halted exception of the PLVexc package and switches PLVexc into "bailout mode." Here is the header for this procedure:
PROCEDURE bailout;
This program takes two steps: it sets a Boolean flag to indicate PLVexc is now bailing out and it then raises the process_halted exception.
You can stop a bailout in progress with a call to nobailout :
PROCEDURE nobailout;
PLVexc never calls this program. It is up to you to decide if you want to stop propagation of the exception and continue processing in your application.
You can also find out if PLVexc is currently bailing out with a call to the bailing_out function:
FUNCTION bailing_out RETURN BOOLEAN;
The bailout procedure is called by the low-level handle procedure if it encounters a bailout error or PLVexc is in bailout mode already. The following IF statement is, in fact, the first line of code in the body of the handle procedure:
IF bailing_out THEN bailout; END IF;
So if a bailout error was previously encountered or a developer calls PLVexc.bailout directly, the handle program short-circuits. It does not record the error. (PLVexc assumes that the error was recorded as specified when it was first handled.) It does not display the error; it just raises the process_halted exception -- again and again for as long as the PLVexc handler programs are called in exception sections of enclosing blocks.
|
|
|
|
| 21.2 PLVtrc: Tracing Execution of PL/SQL Programs |
|
22.2 Application-Specific Exception Packages |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.