10.11 How do I…Handle user-defined exceptions and user-defined errors?Problem
I want to create and use my own exception within the code to treat certain situations like an exception. When certain business rules are violated within a PL/SQL block, I want to invoke the user-defined exception. I also want to know how to create exceptions with a user-defined error number and user-defined error message. How do I create and handle user-defined exceptions?
Technique
A user-defined exception can be declared in the declarative section of a PL/SQL block and raised using the RAISE statement. When an exception is raised, execution is passed to the exception section of the block. The exception handler created for the user-defined exception is executed any time the exception is raised. The RAISE statement can also be used to manually invoke a predefined exception. The built-in procedure RAISE_APPLICATION_ERROR enables you to raise an exception with a user-defined error number and user-defined error message. As seen in How-To 10.10, an exception can be declared in the declarative section of a block, which can be associated with an error code by using the EXCEPTION_INIT pragma compiler directive.
Whenever a predefined or user-defined exception is raised inside a subprogram and there is no exception handler, control is immediately passed to the calling block. Even if the values of the OUT and IN OUT parameters have been modified in the subprogram, they are not returned back to the caller. If the calling block also does not have a handler for the exception, control is propagated back to the calling environment. A handler must exist in the subprogram or the calling block to trap and handle the exception.
Steps
1. Run SQL*Plus and connect as the WAITE user account and run CHP10_36.SQL, which contains a PL/SQL block that makes use of a user-defined exception. The code and resulting output are shown in Figure 10.36.
Lines 1 though 3 contain the declarative section of the block. Line 3 declares an exception variable that can be raised in the executable section of the block and handled in the exception section. Lines 4 through 9 contain the executable section of the block. Line 5 sets the value of a variable to X. Lines 6 through 8 evaluate the value of the variable. If the value of the variable is not in a list of valid values, the RAISE statement raises the exception in line 7. Lines 10 through 14 contain the exception section of the block. Lines 11 and 10 handle the user-defined exception and display the error code and error message using the SQLCODE and SQLERRM built-in functions.
The user-defined exception is raised in the block and handled by the exception handler in lines 11 and 10. The message displayed in SQL*Plus is generated by this exception handler. The error code of 1 stands for a user-defined exception as the error message confirms.
2. Run CHP10_37.SQL in SQL*Plus, as shown in Figure 10.37. The sample stored procedure calls the RAISE_APPLICATION_ERROR system procedure to return a user-defined error code and error message to the calling module. The exception the stored procedure generates can be caught as a user-defined exception in the calling block.
3. Run the CHP10_38.SQL file in SQL*Plus. The PL/SQL block in the file calls the stored procedure created in the last step and handles the error returned. The code and output are shown in Figure 10.38 .
Lines 1 through 3 contain the declarative section of the block. Line 2 declares an exception to handle the error generated by the stored procedure. Line 3 associates the error code returned by the stored procedure with the name of the exception, by using a PRAGMA EXCEPTION directive. Lines 4 through 6 contain the executable section of the block. Line 5 executes the stored procedure. Any errors generated by the stored procedure must be handled, or the PL/SQL block terminates abnormally. The exception handler in lines 8 to 10 handles the exception generated as a result of calling the stored procedure.
The exception created in the declarative section handles the error generated by the stored procedure. The user-defined error number and user-defined error message specified in the call to RAISE_APPLICATION_ERROR procedure in the previous step are now displayed in SQL*Plus, using the SQLCODE and SQLERRM functions.
How It Works
A user-defined exception can be declared in the declarative section of a block and raised using the RAISE statement. When the exception is raised, the associated exception handler is executed. Step 1 presents a PL/SQL block employing a user-defined exception to exit the executable section of the code. If a user-defined exception is raised in the executable section but not handled in the exception section, an error occurs and the block terminates abnormally.
Step 2 creates a stored procedure that returns a user-defined error code and error message, when an exception is raised by calling the RAISE_APPLICATION_ERROR procedure. In Step 3, an exception is declared in the declarative section of a PL/SQL block and associated with a user-defined error code by using the PRAGMA EXCEPTION_INIT compiler directive. The exception generated when the stored procedure is called is handled by a user-defined exception handler in the calling block.
Comments
If a business situation arises such that you need to gracefully exit a PL/SQL block, consider generating a user-defined exception by using the RAISE statement in an IF statement. Alternatively, you can use the RAISE_APPLICATION_ERROR procedure to generate an exception with a user-defined error code. The user-defined error code has to be in the range of -20,000 to -20,999. An exception raised in the exception section cannot be handled by another handler in the exception section and is propagated to the calling environment.