6.4. Handling ExceptionsOnce an exception is raised, the current PL/SQL block stops its regular execution and transfers control to the exception section. The exception is then either handled by an exception handler in the current PL/SQL block or passed to the enclosing block. To handle or trap an exception once it is raised, you must write an exception handler for that exception. In your code, your exception handlers must appear after all the executable statements in your program but before the END statement of the block. The EXCEPTION keyword
The syntax for an exception handler is as follows:
or:
You can have multiple exception handlers in a single exception section. The exception handlers are structured much like a conditional CASE statement, as shown in the following table:
| If the NO_DATA_FOUND exception is raised, then execute the first set of | |||
| If the payment is overdue, then execute the second set of statements. | |||
| If any other exception is encountered, then execute the third set of statements. |
An exception is handled if an exception that is named in a WHEN clause matches the exception that was raised. Notice that the WHEN clause traps errors only by exception name, not by error codes. If a match is found, then the executable statements associated with that exception are run. If the exception that has been raised is not handled or does not match any of the named exceptions, the executable statements associated with the WHEN OTHERS clause
(if present) will be run. Only one exception handler can catch a particular error. After the statements for that handler are executed, control passes immediately out of the block.
The WHEN OTHERS clause is optional; if it is not present, then any unhandled exception is immediately propagated back to the enclosing block, if any. The WHEN OTHERS clause must be the last exception handler in the exception section. If you place any other WHEN clauses after WHEN OTHERS, you will receive the following compilation error:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
6.4.1. Built-in Error Functions
Before exploring the nuances of error handling, let's first review the built-in functions Oracle provides to help you identify, analyze, and respond to errors that occur in your PL/SQL application.
SQLCODE
SQLCODE returns the error code of the most recently raised exception in your block. If there is no error, SQLCODE returns 0. SQLCODE also returns 0 when you call it outside of an exception handler.
Oracle maintains a stack of SQLCODE values. Suppose, for example, that function FUNC raises the VALUE_ERROR exception (-6502). Within the exception section of FUNC, you call a procedure PROC that raises DUP_VAL_ON_INDEX (-1). Within the exception section of PROC, SQLCODE returns -1. When control propagates back up to the exception section of FUNC, however, SQLCODE will still return -6502. Run the sqlcode_test.sql file (available on the book's web site) to see a demonstration of this behavior.
SQLERRM
SQLERRM is a function that returns the error message for a particular error code. If you do not pass an error code to SQLERRM, it returns the error message associated with the value returned by SQLCODE.
If SQLCODE is 0, SQLERRM returns this string:
ORA-0000: normal, successful completionIf SQLCODE is 1 (the generic user-defined exception error code), SQLERRM returns this string:
User-defined exceptionHere is an example of calling SQLERRM to return the error message for a particular code:
1 BEGIN
2 DBMS_OUTPUT.put_line (SQLERRM (-1403));
3* END;
SQL> /
ORA-01403: no data foundThe maximum length string that SQLERRM will return is 512 bytes (in some earlier versions of Oracle, only 255 bytes). Because of this restriction, Oracle recommends that you call DBMS_UTILITY.FORMAT_ERROR_STACK to ensure that you see the full error message string.
The oracle_error_info.pkg and oracle_error_info.tst files provide an example of how you can use SQLERRM to validate error codes.
DBMS_UTILITY.FORMAT_ERROR_STACK
This built-in function, like SQLERRM, returns the message associated with the current error (i.e., the value returned by SQLCODE). It differs from SQLERRM in two ways:
Its length is not restricted; it will return the full error message string.
You cannot pass an error code number to this function; it cannot be used to return the message for a "random" error code.
As a rule, you should call this function inside your exception handler logic to obtain the full error message.
Note that even though the name of the function includes the word "stack," it doesn't return a stack of errors leading back to the line on which the error was originally raised. That job falls to DBMS_UTILITY.FORMAT_ERROR_ BACKTRACE.
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
New to Oracle Database 10g Release 1, this function returns a formatted string that displays a stack of programs and line numbers leading back to the line on which the error was originally raised.
This function closes a significant gap in PL/SQL functionality. In Oracle9i Database and earlier releases, once you handled an exception inside your PL/SQL block, you were unable to determine the line on which the error had occurred (perhaps the most important piece of information to developers). If you wanted to see this information, you would have to allow the exception to go unhandled, at which point the full error backtrace would be displayed on the screen or otherwise presented to the user. This situation is explored in more detail in the following section
DBMS_UTILITY.FORMAT_CALL_STACK
DBMS_UTILITY.FORMAT_CALL_STACK returns a formatted string showing the execution call stack inside your PL/SQL application. Its usefulness is not restricted to error management; you will also find it handy for tracing the execution of your code. If, however, you are not yet running Oracle Database 10g, you can include this function in your exception section and at least see the sequence of program calls that got you to the exception handler. This program is explored in more detail in Chapter 20.
6.4.1.1 More on DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
You should call the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function in your exception handler. It displays the execution stack at the point where an exception was raised. Thus, you can call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE within an exception section at the top level of your stack and still find out where the error was raised deep within the call stack.
Consider the following scenario: I define a procedure proc3, which calls proc2, which in turns calls proc1. The proc1 procedure raises an exception:
CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
DBMS_OUTPUT.put_line ('running proc1');
RAISE NO_DATA_FOUND;
END;
/
CREATE OR REPLACE PROCEDURE proc2 IS
l_str VARCHAR2 (30) := 'calling proc1';
BEGIN
DBMS_OUTPUT.put_line (l_str);
proc1;
END;
/
CREATE OR REPLACE PROCEDURE proc3 IS
BEGIN
DBMS_OUTPUT.put_line ('calling proc2');
proc2;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error stack at top level:');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
/
The only program with an exception handler is the outermost program, proc3. I have placed a call to the backtrace function in proc3's WHEN OTHERS handler. When I run this procedure I see the following results:
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 backtrace');
3 proc3;
4 END;
5 /
Proc3 -> Proc2 -> Proc1 backtrace
calling proc2
calling proc1
running proc1
Error stack at top level:
ORA-06512: at "SCOTT.PROC1", line 4
ORA-06512: at "SCOTT.PROC2", line 5
ORA-06512: at "SCOTT.PROC3", line 4
As you can see, the backtrace function shows at the top of its stack the line in proc1 on which the error was originally raised.
Often, an exception occurs deep within the execution stack. If you want that exception to propagate all the way to the outermost PL/SQL block, it may have to be re-raised within each exception handler in the stack of blocks. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE shows the trace of execution back to the last RAISE in one's session. As soon as you issue a RAISE of a particular exception or re-raise the current exception, you restart the stack that the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function produces. This means that if you want to take advantage of this function, you should take one of the following two approaches:
Call the function in the exception section of the block in which the error was raised. This way you have (and can log) that critical line number, even if the exception is re-raised further up in the stack.
Avoid exception handlers in intermediate programs in your stack, and call the function in the exception section of the outermost program in your stack.
6.4.1.2 Just the line number, please
In a real-world application, the error backtrace could be very long. Generally, the person doing the debugging or support doesn't really want to have to deal with the entire stack; he is mostly going to be interested only in that topmost entry. The developer of the application might even want to display that critical information to the user so that he can immediately and accurately report the problem to the support team.
In this case, it is necessary to parse the backtrace string and retrieve just the topmost entry. I built a utility to do this called the BT package; you can download it from the book's web site. In this package, I provide a simple, clean interface as follows:
/* File on web: bt.pkg */
CREATE OR REPLACE PACKAGE bt
IS
TYPE error_rt IS RECORD (
program_owner all_objects.owner%TYPE
, program_name all_objects.object_name%TYPE
, line_number PLS_INTEGER
);
FUNCTION info (backtrace_in IN VARCHAR2)
RETURN error_rt;
PROCEDURE show_info (backtrace_in IN VARCHAR2);
END bt;
/
The record type, error_rt, contains a separate field for each element of the backtrace that I want to retrieve (owner of the program unit, name of the program unit, and line number within that program). Then, instead of calling and parsing the backtrace function in each exception section, I can call the bt.info function and report on the specifics of the error.
6.4.1.3 Useful applications of SQLERRM
While it is true that you should use DBMS_UTILITY.FORMAT_ERROR_STACK in place of SQLERRM, that doesn't mean SQLERRM is totally irrelevant. In fact, you can use it to answer the following questions:
Is a particular number a valid Oracle error?
What is the error message corresponding to an error code?
As mentioned earlier in this chapter, SQLERRM will return the error message for an error code. If, however, you pass SQLERRM a code that is not valid, it does not raise an exception. Instead, it returns a string in one of the following two forms:
If the number is negative:
ORA-NNNNN: Message NNNNN not found; product=RDBMS; facility=ORA
If the number is positive:
-NNNNN: non-ORACLE exception
You can use these facts to build functions to neatly return information about whatever code you are currently working with. Here is the specification of a package with such programs:
/* File on web: oracle_error_info.pkg */
CREATE OR REPLACE PACKAGE oracle_error_info
IS
FUNCTION is_app_error (code_in IN INTEGER)
RETURN BOOLEAN;
FUNCTION is_valid_oracle_error (
code_in IN INTEGER
, app_errors_ok_in IN BOOLEAN DEFAULT TRUE
, user_error_ok_in IN BOOLEAN DEFAULT TRUE
)
RETURN BOOLEAN;
PROCEDURE validate_oracle_error (
code_in IN INTEGER
, message_out OUT VARCHAR2
, is_valid_out OUT BOOLEAN
, app_errors_ok_in IN BOOLEAN DEFAULT TRUE
, user_error_ok_in IN BOOLEAN DEFAULT TRUE
);
END oracle_error_info;
/
You will find the complete implementation on the book's web site.
6.4.2. Combining Multiple Exceptions in a Single Handler
You can, within a single WHEN clause, combine multiple exceptions together with an OR operator, just as you would combine multiple Boolean expressions:
WHEN invalid_company_id OR negative_balance
THEN
You can also combine application and system exception names in a single handler:
WHEN balance_too_low OR ZERO_DIVIDE OR DBMS_LDAP.INVALID_SESSION
THEN
You cannot, however, use the AND operator because only one exception can be raised at a time.
6.4.3. Unhandled Exceptions
If an exception is raised in your program, and it is not handled by an exception section in either the current or enclosing PL/SQL blocks, that exception is unhandled. PL/SQL returns the error that raised the unhandled exception all the way back to the application environment from which PL/SQL was run. That environment (a tool like SQL*Plus, Oracle Forms, or a Java program) then takes an action appropriate to the situation; in the case of SQL*Plus, a ROLLBACK of any DML changes from within that top-level block's logic is automatically performed.
One key decision to make about your application architecture is whether you want to allow unhandled exceptions
to occur at all. They are handled differently by different front ends, and in some cases none too gracefully. If your PL/SQL programs are being called from a non-PL/SQL environment, you may want to design your outermost blocks or programs to do the following:
Trap any exception that might have propagated out to that point.
Log the error so that a developer can analyze what might be the cause of the problem.
Pass back a status code, description, and any other information needed by the host environment to make a determination about an appropriate action to take.
6.4.4. Propagation of an Unhandled Exception
The scope rules for exceptions determine the block in which an exception can be raised. The rules for exception propagation address the way in which an exception is handled after it is raised.
When an exception is raised, PL/SQL looks for an exception handler in the current block (anonymous block, procedure, or function) of the exception. If it does not find a match, then PL/SQL propagates the exception to the enclosing block of that current block. PL/SQL then attempts to handle the exception by raising it once more in the enclosing block. It continues to do this in each successive enclosing block until there are no more blocks in which to raise the exception (see Figure 6-2). When all blocks are exhausted, PL/SQL returns an unhandled exception to the application environment that executed the outermost PL/SQL block. An unhandled exception halts the execution of the host program.
Figure 6-2. Propagation of an exception through nested blocks
6.4.4.1 Losing exception information
The architecture of PL/SQL exception handling leads to an odd situation regarding local, programmer-defined exceptions: you can lose crucial information (what error occurred?) unless you are careful.
Consider the following situation. I declare an exception as follows:
BEGIN
<<local_block>>
DECLARE
case_is_not_made EXCEPTION;
BEGIN
...
END local_block;
but neglect to include an exception section. The scope of the case_is_not_made exception is inside local_block's execution and exception sections. If the exception is not handled there and instead propagates to the enclosing block, then there is no way to know that the case_is_not_made exception was raised. You really don't know what error was raised, only that some error was raised.
How can this be so? Because all user-defined exceptions have the same error number and error message: 1 and "User-defined error", respectively. Not very informative. Only the exception's name is unique. But outside of local_block, that name, that exception, no longer exists.
As a consequence, when you are working with locally defined (and raised) exceptions, you must always include an exception handler specifically for that error by name.
6.4.4.2 Examples of propagation
Let's look at a few examples
of how exceptions propagate through enclosing blocks. Figure 6-3 shows how the exception raised in the inner block, too_many_faults, is handled by the next enclosing block. The innermost block has an exception section, so PL/SQL first checks to see if too_many_faults is handled in this section. Because it was not handled, PL/SQL closes that block and raises the too_many_faults exception in the enclosing block, Nested Block 1. Control immediately passes to the exception section of Nested Block 1. (The executable statements after Nested Block 2 are not executed.) PL/SQL scans the exception handlers and finds that too_many_faults is handled in this block, so the code for that handler is executed, and control passes back to the main list_my_faults procedure.
Notice that if the NO_DATA_FOUND exception had been raised in the innermost block (Nested Block 2), then the exception section for Nested Block 2 would have handled the exception. Then control would pass back to Nested Block 1, and the executable statements that come after Nested Block 2 would be executed.
In Figure 6-4, the exception raised in the inner block is handled by the outermost block. The outermost block is the only one with an exception section, so when Nested Block 2 raises the too_many_faults exception, PL/SQL terminates execution of that block and raises that exception in the enclosing block, Nested Block 1. Again, this block has no exception section, so PL/SQL immediately terminates Nested Block
Figure 6-3. Propagation of exception handling to first nested block
1 and passes control to the outermost block, the list_my_faults procedure. This procedure does have an exception section, so PL/SQL scans the exception handlers, finds a match for too_many_faults, executes the code for that handler, and then returns control to whatever program called list_my_faults.
6.4.5. Continuing Past Exceptions
When an exception is raised in a PL/SQL block, normal execution is halted and control is transferred to the exception section. You can never return to the execution section once an exception is raised in that block. In some cases, however, the ability to continue past exceptions is exactly the desired behavior.
Consider the following scenario: I need to write a procedure that performs a series of DML statements against a variety of tables (delete from one table, update another, insert into a final table). My first pass at writing this procedure might produce code like the following:
PROCEDURE change_data IS
BEGIN
DELETE FROM employee WHERE ... ;
Figure 6-4. Exception raised in nested block handled by outermost block
UPDATE company SET ... ;
INSERT INTO company_history SELECT * FROM company WHERE ... ;
END;
This procedure certainly contains all the appropriate DML statements. But one of the requirements for this program is that, although these statements are executed in sequence, they are logically independent of each other. In other words, even if the DELETE fails, I want to go on and perform the UPDATE and INSERT.
With the current version of change_data, I can't make sure that all three DML statements will at least be attempted. If an exception is raised from the DELETE, for example, the entire program's execution will halt, and control will be passed to the exception section, if there is one. The remaining SQL statements won't be executed.
How can I get the exception to be raised and handled without terminating the program as a whole? The solution is to place the DELETE within its own PL/SQL block. Consider this next version of the change_data program:
PROCEDURE change_data IS
BEGIN
BEGIN
DELETE FROM employee WHERE ... ;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
BEGIN
UPDATE company SET ... ;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
BEGIN
INSERT INTO company_history SELECT * FROM company WHERE ... ;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END;
With this new format, if the DELETE raises an exception, control is immediately passed to the exception section. But what a difference! Because the DELETE statement is now in its own block, it can have its own exception section. The WHEN OTHERS clause in that section smoothly handles the error by doing nothing. Control is then passed out of the DELETE's block and back to the enclosing change_data procedure.
Execution in this enclosing block then continues to the next statement in the procedure. A new anonymous block is then entered for the UPDATE statement. If the UPDATE statement fails, the WHEN OTHERS in the UPDATE's own exception section traps the problem and returns control to change_data, which blithely moves on to the INSERT statement (contained in its very own block).
Figure 6-5 shows this process for two sequential DELETE statements.
Figure 6-5. Sequential DELETEs, using two different approaches to scope
To summarize: a raised exception will always be handled in the current blockif there is a matching handler present. You can create a "virtual block" around any statement(s) by prefacing it with a BEGIN and following it with an EXCEPTION section and an END statement. In this way you can control the scope of failure caused by an exception by establishing "buffers" of anonymous blocks in your code.
You can also take this strategy a step further and move the code you want to isolate into separate procedures or functions. Of course, these named PL/SQL blocks may also have their own exception sections and will offer the same protection from total failure. One key advantage of using procedures and functions is that you hide all the BEGIN-EXCEPTION-END statements from the mainline program. The program is then easier to read, understand, maintain, and reuse in multiple contexts.
6.4.6. Writing WHEN OTHERS Handling Code
Include the WHEN OTHERS clause in the exception section to trap any otherwise unhandled exceptions. Because you have not explicitly handled any specific exceptions, you will very likely want to take advantage of the built-in error functions, such as SQLCODE and DBMS_UTILITY.FORMAT_ERROR_STACK, to give you information about the error that has occurred.
Combined with WHEN OTHERS, SQLCODE provides a way for you to handle different, specific exceptions without having to use the EXCEPTION_INIT pragma. In the next example, I trap two parent-child exceptions, -1 and -2292, and then take an action appropriate to each situation:
CREATE OR PROCEDURE add_company (
id_in IN company.ID%TYPE
, name_in IN company.name%TYPE
, type_id_in IN company.type_id%TYPE
)
IS
BEGIN
INSERT INTO company (ID, name, type_id)
VALUES (id_in, name_in, type_id_in);
EXCEPTION
WHEN OTHERS
THEN
/*
|| Anonymous block inside the exception handler lets me declare
|| local variables to hold the error code information.
*/
DECLARE
l_errcode PLS_inTEGER := SQLCODE;
BEGIN
IF l_errcode = -1
THEN
-- Duplicate value for unique index. Either a repeat of the
-- primary key or name. Display problem and re-raise.
DBMS_OUTPUT.put_line
( 'Company ID or name already in use. ID = '
|| TO_CHAR (id_in)
|| ' name = '
|| name_in
);
RAISE;
ELSIF l_errcode = -2291
THEN
-- Parent key not found for type. Display problem and re-raise.
DBMS_OUTPUT.put_line (
'Invalid company type ID: ' || TO_CHAR (type_id_in));
RAISE;
ELSE
RAISE;
END IF;
END; -- End of anonymous block.
END add_company;
You should use WHEN OTHERS with care, because it can easily "swallow up" errors and hide them from the outer blocks and the user. Specifically, watch out for WHEN OTHER handlers that do not re-raise the current exception or raise some other exception in its place. If WHEN OTHERS does not propagate out an exception, then the outer blocks of your application will never know that an error occurred.
No comments:
Post a Comment