 | Chapter 8 Exception Handlers |  |
A PL/SQL program is an anonymous block, a procedure, or a function. This program, or "highest-level" block, can call other procedures or functions, or nest an anonymous block within that block. So at any given point in execution, there might be several layers of PL/SQL blocks nested within other blocks. Each PL/SQL block can have its own exception section, or it can be totally void of exception handlers.
To determine the appropriate exception-handling behavior, PL/SQL follows rules regarding:
The PL/SQL block or blocks in which an exception can be raised and handled.
The way in which an exception is passed back through enclosing blocks until it is handled or is resolved to be an unhandled exception
Let's look at these two properties of exceptions.
The scope of an exception is that portion of the code which is "covered" by that exception. An exception covers a block of code if it can be raised in that block. The following table shows the scope for each of the different kinds of exceptions:
| Exception Type | Description of Scope |
|---|---|
| Named system exceptions | These exceptions are globally available because they are not declared in or confined to any particular block of code. You can raise and handle a named system exception in any block. |
| Named programmer-defined exceptions | These exceptions can only be raised and handled in the execution and exception sections of the block in which they are declared (and all nested blocks). |
| Unnamed system exceptions | These exceptions can be handled in any PL/SQL exception section via the WHEN OTHERS section. If they are assigned a name, then the scope of that name is the same as that of the named programmer-defined exception. |
| Unnamed programmer-defined exceptions | This exception is only defined in the call to RAISE_APPLICATION_ERROR and then is passed back to the calling program. |
Consider the following example of the exception overdue_balance declared in procedure check_account. The scope of that exception is the check_account procedure, and nothing else:
PROCEDURE check_account (company_id_in IN NUMBER)
IS
overdue_balance EXCEPTION;
BEGIN
... executable statements ...
LOOP
...
IF ... THEN
RAISE overdue_balance;
END IF;
END LOOP;
EXCEPTION
WHEN overdue_balance THEN ...
END;I can RAISE the overdue_balance inside the check_account procedure, but I cannot raise that exception from a program that calls check_account. The following anonymous block will generate the associated compile error:
DECLARE company_id NUMBER := 100; BEGIN check_account (100); EXCEPTION WHEN overdue_balance /* PL/SQL cannot resolve this reference. */ THEN ... END; PLS-00201: identifier "OVERDUE_BALANCE" must be declared
The check_account procedure is a "black box" as far as the anonymous block is concerned. Any identifiers -- including exceptions -- which are declared inside check_account are invisible outside of that program.
When you declare an exception in a block, it is local to that block, but global to all the blocks which are enclosed by that block (nested blocks). In the version of check_account shown in the following example, the procedure contains an anonymous subblock which also raises the overdue_balance. Because the subblock is enclosed by the procedure block, PL/SQL can resolve the reference to that exception:
PROCEDURE check_account (company_id_in IN NUMBER)
IS
overdue_balance EXCEPTION;
BEGIN
... executable statements ...
-- Start of sub-block inside check_account
BEGIN
... statements within sub-block ...
RAISE overdue_balance; -- Exception raised in sub-block.
END;
-- End of sub-block inside check_account
LOOP
...
IF ... THEN
RAISE overdue_balance; -- Exception raised in main block.
END IF;
END LOOP;
EXCEPTION
WHEN overdue_balance THEN ... -- Exception handled in main block.
END;When the overdue_balance exception is raised in either the subblock or the main block, control is transferred immediately to the main block -- the only exception section in the entire procedure. Because overdue_balance was declared for the whole procedure, the name is known throughout all subblocks.
You never have to declare a named system exception because they have all been declared in the STANDARD package, which is instantiated as soon as you run any PL/SQL code. You can, on the other hand, declare your own exceptions with the same name as a previously defined system exception, as shown below:
DECLARE no_data_found EXCEPTION; BEGIN
This locally declared exception will take precedence over the system exception. As a result, the following exception handler will not trap an error caused by an implicit query that returns no rows:
EXCEPTION
WHEN no_data_found -- This is MY exception, not PL/SQL's.
THEN
...
END;If I really want to name a local exception the same as a predefined exception and handle the latter in that same PL/SQL block, I need to use the dot notation on the predefined exception. This notation distinguishes between the two and allows PL/SQL to find the handler for the predefined exception. I can then handle those two different exceptions separately, or I can combine them in a single expression:
EXCEPTION
WHEN no_data_found
THEN
DBMS_OUTPUT.PUT_LINE ('My own local exception');
WHEN STANDARD.NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE ('The predefined exception');
END;or:
EXCEPTION
WHEN no_data_found OR STANDARD.NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE ('Could not find the data');
END;Of course, the best solution is to never declare exceptions with the same name as a named system exception.
You cannot declare the same exception more than once in a single block, but you can declare an exception in a nested block with the same name as that of an enclosing block. When you do this, the declaration local to that nested block takes precedence over the global exception. When you raise that exception, you are raising the local exception, which is a completely different exception from the outer exception, even though they share the same name.
In the following version of check_account, my nested block contains its own declaration of the overdue_balance exception:
1 PROCEDURE check_account (company_id_in IN NUMBER)
2 IS
3 /* Main block exception declaration */
4 overdue_balance EXCEPTION;
5 BEGIN
6 ... executable statements ...
7
8 /* Start of sub-block inside check_account */
9 DECLARE
10 /* Sub-block exception declaration */
11 overdue_balance EXCEPTION;
12 BEGIN
13 ... statements within sub-block ...
14
15 /* Exception raised in sub-block. */
16 RAISE overdue_balance;
17 END;
18 /* End of sub-block inside check_account */
19
20 LOOP
21 ...
22 IF ... THEN
23 /* Exception raised in main block. */
24 RAISE overdue_balance;
25 END IF;
26 END LOOP;
27
28 EXCEPTION
29 /* Exception handled in main block. */
30 WHEN overdue_balance
31 THEN
32 DBMS_OUTPUT.PUT_LINE ('Balance is overdue. Pay up!');
33 END;This program will compile without a hitch. Even though the overdue_balance exception is declared twice, each declaration takes place in a different PL/SQL block, hence in a different scope. The procedure-level overdue_balance is declared on line 4. The exception of the same name for the nested block is declared on line 11. Following these declarations, there are two RAISE statements -- one within the nested block on line 16 and one in the main body of the procedure on line 24. Finally, there is just a single exception section and one handler for overdue_balance on line 30.
Well, the overdue_balance exception is certainly declared in every block in which it is raised. Yet the exception-handling behavior of check_account may not be as you would expect. What happens, for example, when the RAISE statement on line 16 executes? Do you see this:
'Balance is overdue. Pay up!'
or this:
ORA-06501: PL/SQL: unhandled user-defined exception ORA-6512: at "USER.CHECK_ACCOUNT", line N
You will, in fact, have raised an unhandled exception when you RAISE overdue_balance in the nested block. How can this be?
Remember that the nested block does not have its own exception section -- yet it does have its own, locally declared exception. So when the exception is raised on line 16, the nested block cannot handle the exception. Instead, that exception (declared only in that nested block) is passed on to the enclosing block and PL/SQL tries to handle it there.
As soon as control passes to the enclosing block, however, the nested block terminates and all local identifiers are erased. This includes the exception which was just raised. It is no longer defined and therefore cannot be handled in the outer block, even though it seems to have an handler for precisely that exception.
But there is still a glitch: the enclosing block doesn't know anything about the local overdue_balance, only its own rendition. And even though they appear to have the same name, these exceptions are different exceptions as far as the compiler is concerned. As a result, the nested block overdue_balance exception goes unhandled.
How can you get around this problem? First of all, you should avoid such duplication of exception names. This only makes the code very hard to understand and follow. But if you insist on these overlapping names, you can take any of the following steps:
Raise the procedure-level overdue_balance exception within the subblock if the enclosing block has a name (if it is a procedure or a function). You can use dot notation to distinguish the local exception from its global counterpart as follows:
RAISE check_account.overdue_balance;
Make sure that the locally raised overdue_balance is handled by the main check_account exception section by including a WHEN OTHERS clause to trap any exceptions not otherwise handled.
Remove the local declaration of overdue_balance so this exception is declared only once in the program unit.
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, once it has been raised.
When an exception is raised, PL/SQL looks for an exception handler in the current block (anonymous block, procedure, or function) for this 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 that exception 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 8.3). 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.
One very direct consequence of this propagation method is that if PL/SQL cannot locate an exception handler in the current block for a local, programmer-defined exception, the exception will not be handled at all. The exception is not recognized outside of the current block, so propagating to enclosing blocks will never cause the exception to be handled (unless you use the blanket WHEN OTHERS handler).
Let's look at a few examples of the way exceptions propagate through enclosing blocks.
Figure 8.4 shows 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 the 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, after which 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 which come after Nested Block 2 would be executed.
In Figure 8.5, 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 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.
|  | |
| 8.3 Types of Exceptions |  | 8.5 Raising an Exception |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.