22.1.6.1 The handle procedure

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.

1. 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.

2. 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;

22.1.6.2 The high-level handlers

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?

22.1.6.3 Using the high-level handlers

Let's look at some examples of using these handlers.

1. 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;

2. 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;

3. 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;

22.1.6.4 Defining error messages with PLVmsg

There are two scenarios under which PLVexc obtains the text of an error message from the PLVmsg package:

1. When you use an integer version of a high-level handler (you pass in an error number).

2. 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.

22.1.6.5 Integrating PLVexc with PLVtrc

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.

22.1.7.1 Logging errors

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.

22.1.7.2 Showing errors

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.

22.1.7.3 Using the record toggles

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.

22.1.10.1 Managing the bailout error list

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.

22.1.10.2 Starting and stopping the bailout

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.