Once 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.
Remember: once an exception is raised, the execution section is terminated. You cannot return to that body of code.
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 indicates the start of the exception section and the individual exception handlers.
The structure of the exception section is very similar to a CASE statement (which is not available in PL/SQL):
EXCEPTION WHEN exception_name1 THEN <executable statements> WHEN exception_nameN THEN <executable statements> WHEN OTHERS THEN <executable statements> END;
where exception_name1 is the name of the first exception handled in the section, exception_nameN is the name of the last named exception handled in the section, and the WHEN OTHERS clause provides the "otherwise" portion of the CASE statement. Of course, the code for each exception handler need not be on the same line, as shown in the preceding example. Every executable statement after a THEN and before the next WHEN or the final END statement belongs to the exception named by the previous WHEN statement, and is executed when that exception is raised.
The WHEN OTHERS clause must be the last exception handler in the exception section. If you place any other WHEN clauses after WHEN OTHER, you will receive the following compilation error:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
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 THEN
You cannot, however, use the AND operator, because only one exception can be raised at a time.
If an exception is raised in your program and that exception 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 which raised an unhandled exception all the way back to the application environment from which PL/SQL was run. That application environment (a tool like SQL*Plus, Oracle Forms, or a Powerbuilder program) then takes an action appropriate to the situation.
A well-designed application will not allow unhandled exceptions to occur. The best way to avoid unhandled exceptions is to make sure that the outermost PL/SQL block (whether it is an anonymous block in SQL*Plus or a stored procedure in the database) contains a WHEN OTHERS clause in its exception section.
You can use the WHEN OTHERS clause in the exception section to trap all otherwise unhandled exceptions, including internal errors which are not predefined by PL/SQL. Once inside the exception handler, however, you will often want to know which error occurred. You can use the SQLCODE function to obtain this information.
Consider the following situation. My application maintains companies and orders entered for those companies. My foreign key constraint on company_id in the orders table guarantees that I cannot delete a company if there are still child records (orders) in the database for that company. The following procedure deletes companies and handles any exceptions which might arise:
PROCEDURE delete_company (company_id_in IN NUMBER) IS BEGIN DELETE FROM company WHERE company_id = company_id_in; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUTLINE (' Error deleting company.'); END;
Notice the generic nature of the error message. I don't have any idea what brought me there, so I cannot pass on much useful information to the users. Did the delete fail because there are orders still present? Then perhaps I would want to delete them and then delete the company. Fortunately, Oracle provides two functions, SQLCODE and SQLERRM, which return, respectively, the error code and the error message resulting from the most recently raised exception. (These two functions are described in detail in Chapter 13, Numeric, LOB, and Miscellaneous Functions , Numeric, LOB, and Miscellaneous Functions .)
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 both of the parent-child exceptions, -2292 and -2291, and then take an action appropriate to each situation:
PROCEDURE delete_company (company_id_in IN NUMBER) IS BEGIN DELETE FROM company WHERE company_id = company_id_in; EXCEPTION WHEN OTHERS THEN /* || Anonymous block inside the exception handler lets me declare || local variables to hold the error code information. */ DECLARE error_code NUMBER := SQLCODE; error_msg VARCHAR2 (300) := SQLERRM; BEGIN IF error_code = -2292 THEN /* Child records found. Delete those too! */ DELETE FROM employee WHERE company_id = company_id_in; /* Now delete parent again. */ DELETE FROM company WHERE company_id = company_id_in; ELSIF error_code = -2291 THEN /* Parent key not found. */ DBMS_OUTPUT.PUTLINE (' Invalid company ID: '||TO_CHAR (company_id_in)); ELSE /* This is like a WHEN OTHERS inside a WHEN OTHERS! */ DBMS_OUTPUT.PUTLINE (' Error deleting company, error: '||error_msg); END IF; END; -- End of anonymous block. END delete_company;
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 go home again," and 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 which 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 ... ; 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, in spite of the fact that 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 cannot make sure that all three DML statements will at least be attempted. If an exception is raised from the DELETE, for example, then the entire program's execution will halt and control will be passed to the exception section (if there is one). The remaining SQL statements will not 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 now what a difference! Because the DELETE statement is in its own block, it has 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 8.8 shows this process for two sequential DELETE statements.
To summarize: a raised exception will always be handled in the current block -- if there is a matching handler present. You can always 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. So 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 its own procedures or functions. These named PL/SQL blocks may also, of course, have their own exception sections and will offer the same protection from total failure. The 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, and maintain.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.