On Exceptions and Rules
Best practices for where, when, and how to handle exceptions
By Steven Feuerstein
July/August 2008
I recently learned that if an exception is raised in the declaration section of my block, that block's exception section cannot handle the exception. That doesn't seem right. Why does PL/SQL work this way, and what does it mean for my coding practices?
If an exception is raised in the declaration section of your block, that exception will propagate out of the block unhandled.
PL/SQL behaves like this (or, to be more accurate, the Oracle PL/SQL development team decided to implement exception handling like this) because until local variables and constants are fully elaborated, you don't have a viable subprogram with which to work. Suppose the declaration-raised exception were handled inside that subprogram. To what could you refer inside the exception handler? You couldn't be sure that any of your local variables were initialized.
The key question is: How does this behavior affect the way we should write our code? Before answering this question, let's explore when we are likely to encounter this issue.
Exceptions in the declaration section occur when you try to initialize a variable declared in that section in a way that raises an exception. The most common exception raised surely must be ORA-06502 or VALUE_ERROR, which occurs (to name just two scenarios) when you try to assign a string value that is too large for the variable and when you try to assign a non-numeric value to a number. For example
DECLARE l_name VARCHAR2(5) := 'STEVEN'; l_age NUMBER := '49 Years Old'; BEGIN
This same rule for exceptions applies to initializing variables declared in a package (outside of any subprogram). If an exception occurs when you try to initialize a package-level variable, that exception will propagate unhandled out of the package, even if the initialization section contains an exception section. In such a situation, the PL/SQL runtime engine still registers the package as initialized and allows you to continue to reference subprograms and variables in the package.
To understand this, consider this sequence of steps and PL/SQL statements:
1. I've compiled a package, valerr, that assigns a too-large value to a package-level string. The package body includes a WHEN OTHERS exception section (see Listing 1).
Code Listing 1: Package assigning a too-large value to a package-level string
PACKAGE valerr
IS
FUNCTION little_name RETURN VARCHAR2;
END valerr;
PACKAGE BODY valerr
IS
g_name VARCHAR2 (1) := 'Liu';
FUNCTION little_name RETURN VARCHAR2
IS
BEGIN
RETURN g_name;
END little_name;
BEGIN
DBMS_OUTPUT.put_line ('Before I show you the name... ');
EXCEPTION
WHEN OTHERS
THENDBMS_OUTPUT.put_line ( 'Trapped the error: ' || DBMS_UTILITY.format_error_stack ()
);
RAISE;
END valerr;
2. Now I try to run the valerr.little_name function; the exception goes unhandled:
SQL> BEGIN
2 DBMS_OUTPUT.put_line ('Name: ' || valerr.little_name);
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value
error: character string buffer too small
ORA-06512: at "HR.VALERR", line 3
ORA-06512: at line 2
That's precisely what one would expect.
3. But now I try to call this function a second timeand no exception is raised:
SQL> BEGIN
2 DBMS_OUTPUT.put_line ('Name: ' || valerr.little_name);
3 END;
4 /
Name:
PL/SQL procedure successfully completed.
The package has been marked as initialized, and the PL/SQL runtime engine doesn't try to initialize it again, so any code in the package's initialization section never executes. Yet you can still run all the programs in the package, which can lead to a lot of confusionand an impression that this error cannot be reproduced.
If you ever encounter this scenario, simply reconnect to your schema. You will then be able to reproduce the error, because Oracle Database will now need to attempt to initialize the package for the new session.
Note also that Oracle considers this behavior a bug (number 5658561). If you are concerned about this behavior and need Oracle to change it, I suggest that you log on to Oracle MetaLink and add to this bug your own explanation of how the current behavior harms your applications.
Should this fact about exception handling change how you write your code? I think so. You might sometimes want an exception in the declaration section to propagate unhandled, although probably in most cases, you would rather trap the exception within that block and log the error information.
Doing this is simple: Just don't assign default values to variables in the declaration section. Instead, create a local initialization procedure and assign all default values in that procedure. Then call the initialization procedure in the first line in the executable section of the program, so any exception raised can be trapped in that program. For example, instead of writing a procedure like this:
PROCEDURE process_data
IS
l_name VARCHAR2 (10) := 'Steven Feuerstein';
BEGIN
DBMS_OUTPUT.put_line (l_name);
EXCEPTION
WHEN OTHERS
THEN DBMS_OUTPUT.put_line ( 'Trapped the error: ' || DBMS_UTILITY.format_ error_stack ()
);
RAISE;
END process_data;
Do this instead:
PROCEDURE process_data
IS
l_name VARCHAR2 (10);
PROCEDURE initialize
IS
BEGIN
l_name := 'Steven Feuerstein';
END initialize;
BEGIN
initialize;
DBMS_OUTPUT.put_line (l_name);
EXCEPTION
WHEN OTHERS
THEN DBMS_OUTPUT.put_line ( 'Trapped the error: '|| DBMS_UTILITY.format_error_stack ()
);
RAISE;
END process_data;
Now when I run the revised process_data procedure, the error is trapped and handled before it is reraised:
SQL> BEGIN 2 process_data; 3 END; 4 / Trapped the error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small BEGIN * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "HR.PROCESS_DATA", line 19 ORA-06512: at line 2
The same would be true for a package. In the revised valerr procedure in Listing 2, the initialization section simply calls the initialize procedure.
Code Listing 2: Initialization section calling the initialize procedure
1 PACKAGE BODY valerr
2 IS
3 g_name VARCHAR2 (1);
4
5 FUNCTION little_name
6 RETURN VARCHAR2
7 IS
8 BEGIN
9 RETURN g_name;
10 END little_name;
11
12 PROCEDURE initialize
13 IS
14 BEGIN
15 g_name := 'Lu';
16 END initialize;
17 BEGIN
18 initialize;
19 EXCEPTION
20 WHEN OTHERS
21 THEN
22 DBMS_OUTPUT.put_line ( 'Trapped the error: ' || DBMS_UTILITY.format_error_stack ()
23 );
24 RAISE;
25 END valerr;
Now that I've given you my advice (move assignments of default values to a separate initialization subprogram), I must admit that I have two concerns about it. First, you can't follow this advice for constants. The default value must be assigned at the time of declaration. Second, in the revised valerr package (in Listing 2), my variable (g_name) is declared on line 3 but its value isn't assigned until line 15. In a more typical package, variables will still be declared in the very first lines of the package, but that initialization code will be hundreds, perhaps even thousands, of lines away. Personally, I don't like all that distance.
Assigning my default value on the same line as the declaration of the variable seems easier to understand. All my information is in one place. Do I have to sacrifice this readability for improved error handling? That's a reasonable trade-off, but a compromise could make the most sense here.
Here's what I suggest: If the default value for your variable or constant is a literal, assign the default on the same line with the declaration, but if the default value is the result of an expression, move its assignment down to the initialization procedure.
This approach will improve the readability of your code while minimizing the risk of an unhandled exception. The risk should be minimal, because we should be paying enough attention to our code as we write it to recognize that we have just assigned a value that is of the wrong type or the wrong size. Of course, if all the variables are assigned literals, no initialization subprogram will be needed (see Listing 3).
Code Listing 3: Variables assigned literal values
PROCEDURE process_data
IS
l_name VARCHAR2 (100) := 'Steven Feuerstein';
l_books_sold PLS_INTEGER;
PROCEDURE initialize
IS
BEGIN l_books_sold := book_counter.in_circulation ('Oracle PL/SQL Programming');
END initialize;
BEGIN
initialize;
DBMS_OUTPUT.put_line (
l_name
|| ' sold '
|| l_books_sold
|| ' books.');
EXCEPTION
WHEN OTHERS
THEN
q$error_manager.raise_unanticipated;
RAISE;
END process_data;
And to trap declaration-based exceptions, you can use block statements. A block statement is a DECLARE-BEGIN-END section that you place inside your exception section. Because this statement can have its own exception section, you can immediately trap the exception and either fix the problem or log and reraise the error.
Block statements enable you to defer declaring variables until they are used in the program. "Just in time" declarations help you avoid a long, single declaration section at the top of large subprograms that contain declarations for each variable used in the subprogram.
Suppose, for example, that in the process_data procedure, I don't work with the 1_books_sold variable until line 245 of that procedure. Rather than declare that variable alongside l_name, which is used immediately in the procedure, I can wait until later in my program and use a block statement. Then I can trap the exception that might have been raised in the declaration section. Listing 4 contains a rewrite of process_data that illustrates the use of a block statement.
Code Listing 4: PROCESS_DATA revision with block statement
PROCEDURE process_data
IS
l_name VARCHAR2 (100) := 'Steven Feuerstein';
BEGIN
/*
Immediate use of l_name
*/
IF l_name IS NOT NULL
THEN
... lots more code here ...
END IF;
/*
Lots and lots of code ...
Then I use a block statement to declare l_books_sold
right in the area of the program in which it is needed.
*/
<check_books_sold>
DECLARE
l_books_sold PLS_INTEGER;
BEGINl_books_sold := book_counter.in_circulation ('Oracle PL/SQL Programming');
IF l_books_sold > 1000
THEN
... lots more code here ...
END IF;
EXCEPTION
WHEN VALUE_ERROR
THEN q$error_manager.raise_unanticipated ('Problem initializing l_books_sold!');
RAISE;
END check_books_sold;
... and more code here ...
END process_data;
One final point: Beginning with Oracle Database 10g Release 1, the PL/SQL compiler can issue warnings about the quality of our code. For example, it will warn us that some of the lines of code in our subprogram will never be run or are "unreachable" (PLW-6002). It would be great if Oracle would add a warning for code like this:
DECLARE
l_name VARCHAR2(5) := 'STEVEN';
l_age NUMBER := '49 Years Old';
BEGIN
so that I don't have to wait till I run my program to discover my problem.
Application Source Code Inside an Exception HandlerI was taught that it is a bad practice to put application source code inside an exception handler. We should be able to remove all our exception sections, andassuming no errorsour code should work the same. But I've run into lots of situations where I execute a SELECT INTO (an implicit one-row query), expecting it to return no rows (in other words, that's the correct result). However, Oracle Database raises a NO_DATA_FOUND exception, and I then have to write application logic down in the exception section. Should I never use an implicit SELECT INTO statement in my code?
You are precisely right: it is considered a bad practice to place anything but exception management code in an exception handler. If you put application code inside a WHEN clause, other developers must know to look in this section for application logic. Because that is not the norm, this logic is often overlooked.
So let's agree: place application code inside a WHEN clause only when it's necessary to handle the error (usually, this involves logging and reraising the exception). As you so rightly point out, this raises a conundrum when you write a SELECT INTO statement in your executable section. Does that mean that you should never write SELECT INTOs in your programs? Let's explore this issue.
The implicit SELECT INTO will raise NO_DATA_FOUND if no rows are found and TOO_MANY_ROWS if more than one row is found. These two exceptions need different handling. Llewellyn suggests that we group all exceptions into three categories: deliberate, unfortunate , and unexpected.
In deliberate exceptions, the code deliberately raises an exception as part of its normal behavior. UTL_FILE.GET_LINE, which raises NO_DATA_FOUND when you read past the end of file, is a perfect example of a program that raises a deliberate exception.
Unfortunate exceptions are those where an exception has been raised that may not constitute an error in your application logic. It might, for example, simply be a different data condition. NO_DATA_FOUND, raised by a SELECT INTO, is an unfortunate exception.
A "hard error" that was raised, that you did not expect, and that may indicate a serious problem in your application is an unexpected error. TOO_MANY_ROWS is a classic unexpected error; it indicates that you have duplicate values for a primary key or unique index.
Before you start building your next application, decide on a strategy for handling exceptions of these three types. Then, when you run into a particular exception, figure out which category it falls into and take the appropriate action. Here are the guidelines I follow for these three exception types:
Deliberate. Redesign your program so that you can avoid placing application logic in the exception section. For example, in one way to apply this rule to UTL_FILE.GET_LINE, the process_file procedure in Listing 5 reads the contents of a file and then processes each line it read. Check out the loop in lines 16 through 18: it looks like an infinite loop (it contains no EXIT statement), but, in fact, it will stop when UTL_FILE raises NO_DATA_FOUND.
Code Listing 5: PROCESS_FILE procedure calls UTL_FILE.GET_LINE directly
1 PROCEDURE process_file (dir_in IN VARCHAR2, file_in IN VARCHAR2) 2 IS 3 TYPE line_t IS TABLE OF VARCHAR2 (32767) 4 INDEX BY PLS_INTEGER; 5 6 l_file UTL_FILE.file_type; 7 l_lines line_t; 8 BEGIN 9 l_file := 10 UTL_FILE.fopen (LOCATION => dir_in 11 , filename => file_in 12 , open_mode => 'R' 13 , max_linesize => 32767 14 ) ; 15 16 LOOP 17 UTL_FILE.get_line (l_file, l_lines (l_lines.COUNT + 1)); 18 END LOOP; 19 EXCEPTION 20 WHEN NO_DATA_FOUND 21 THEN 22 /* Process each line */ 23 FOR indx IN 1 .. l_lines.COUNT 24 LOOP 25 do_stuff_with_line (l_lines (indx)); 26 END LOOP; 27 28 UTL_FILE.fclose (l_file); 29 END process_file;
So my exception section traps that error and then processes each line. Unfortunately, that line processing code is in the exception section. What's a programmer to do?
Never call UTL_FILE.GET_LINE directly! Listing 6 shows a rewrite of this procedure that addresses the problem. I create a local module, get_next_line, that calls UTL_FILE.GET_LINE for me. It traps NO_DATA_FOUND and returns TRUE for the OUT Boolean argument to indicate end-of-file.
Code Listing 6: PROCESS_FILE procedure revision calls a local module
1 PROCEDURE process_file (dir_in IN VARCHAR2, file_in IN VARCHAR2)
2 IS
3 TYPE line_t IS TABLE OF VARCHAR2 (32767)
4 INDEX BY PLS_INTEGER;
5
6 l_file UTL_FILE.file_type;
7 l_lines line_t;
8 l_eof BOOLEAN := FALSE;
9
10 PROCEDURE get_next_line (line_out OUT VARCHAR2, eof_out OUT BOOLEAN)
11 IS
12 BEGIN
13 UTL_FILE.get_line (l_file, line_out);
14 eof_out := FALSE;
15 EXCEPTION
16 WHEN NO_DATA_FOUND
17 THEN
18 line_out := NULL;
19 eof_out := TRUE;
20 END get_next_line;
21 BEGIN
22 l_file :=
23 UTL_FILE.fopen (LOCATION => dir_in
24 , filename => file_in
25 , open_mode => 'R'
26 , max_linesize => 32767
27 );
28
29 WHILE (NOT l_eof)
30 LOOP
31 get_next_line (l_lines (l_lines.COUNT + 1), l_eof);
32 EXIT WHEN l_eof;
33 END LOOP;
34
35 /* Process each line */
36 FOR indx IN 1 .. l_lines.COUNT
37 LOOP
38 do_stuff_with_line (l_lines (indx));
39 END LOOP;
40
41 UTL_FILE.fclose (l_file);
42 END process_file;
I then write a WHILE loop that shows under what circumstances the loop will terminate. Immediately after the loop, I finish up with the rest of my application logic and close the file.
The code you'll need to write to work around a deliberate exception will, of course, vary for each exception (and the circumstances that raise it).
Unexpected. The guideline for handling unexpected exceptions is straightforward. In general, you should log the error, along with any application-specific information that will help you understand what caused it. And then you should reraise the error to stop the outer block from continuing to execute.
Avoid hard-coding INSERT statements into your log table, and instead rely on a single, reusable, and generic error management package that will take care of all the administrative details for you.
Unfortunate. Now let's discuss what to do about unfortunate exceptions such as NO_DATA_FOUND. As with the deliberate exception, the general rule is to avoid having to put application logic into the exception section. The way to do that with an unfortunate exception is to make it possible for the programmer to choose whether an exception should be raised.
To demonstrate this approach with NO_DATA_FOUND, suppose that Sam has written a program that returns the ID for a department when provided the department name:
FUNCTION id_for_name ( department_name_in IN departments.department_name%TYPE )
RETURN departments.department_id%TYPE
IS
l_return departments.department_id%TYPE;
BEGIN
SELECT department_id
INTO l_return
FROM departments
WHERE department_name = department_name_in;
RETURN l_return;
END id_for_name;
Sandra needs to write a batch process that reads rows from a staging table containing department data. If that department already exists, she must submit a request through Oracle Advanced Queuing to update that department. If the department does not exist, she submits a request to add a new department. She writes her program, taking advantage of Sam's existing code, which makes them both feel good:
PROCEDURE load_from_staging_table
IS
l_id departments.department_id%TYPE;
BEGIN
FOR dept_rec IN (SELECT *
FROM dept_staging_table)
LOOP
BEGIN
l_id := id_for_name (dept_rec.department_name);
submit_update_request (dept_rec);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
submit_add_request (dept_rec);
END;
END LOOP;
END load_from_staging_table;
If the department name is not found in the table, the id_for_name function raises NO_DATA_FOUND. So Sandra creates an anonymous block inside the loop, traps the exception, puts the "request to add a new department" logic (submit_add_request) in the exception section, and keeps on going.
However, this is precisely what we want to avoid: application logic in the exception section. Again, what's a programmer to do?
To address this drawback, rewrite the id_for_name functionand, by extension, all single-row queries and lookup functions (see Listing 7). This approach has several key features. First, a new parameter, propagate_if_ndf_in, specifies whether the NO_DATA_FOUND exception (when raised by the SELECT INTO) will be propagated out of the function.
Code Listing 7: ID_FOR_NAME function revision
1 FUNCTION id_for_name ( 2 department_name_in IN departments.department_name%TYPE 3 , propagate_if_ndf_in IN BOOLEAN := FALSE 4 , ndf_value_in IN departments.department_id%TYPE := NULL 5 ) 6 RETURN departments.department_id%TYPE 7 IS 8 l_return departments.department_id%TYPE; 9 BEGIN 10 SELECT department_id 11 INTO l_return 12 FROM departments 13 WHERE department_name = department_name_in; 14 15 RETURN l_return; 16 EXCEPTION 17 WHEN NO_DATA_FOUND 18 THEN 19 IF propagate_if_ndf_in 20 THEN 21 RAISE; 22 ELSE 23 RETURN ndf_value_in; 24 END IF; 25 WHEN TOO_MANY_ROWS 26 THEN 27 q$error_manager.raise_unanticipated 28 (text_in => 'Multiple rows found for department name' 29 , name1_in => 'DEPARTMENT_NAME' 30 , value1_in => department_name_in 31 ); 32 END id_for_name;
Second, a new parameter, ndf_value_in, provides the value that will be used to indicate that no data was found, if the exception is not propagated. You might be tempted to simply pass back NULL to indicate "no data found," but that value (or, rather, lack of a value) may sometimes be a legitimate column value. So why hard-code it?
Third, if NO_DATA_FOUND is raised, then it is propagated out of the function with a reraise (RAISE; on line 21) only if the user has asked for that behavior. Otherwise, the function returns the "no data found" indicator value.
Last, if TOO_MANY_ROWS is raised, the error manager utility logs the error, including the department ID that caused the trouble, and propagates the exception out unhandled.
With this new version of the id_for_name function, Sandra can now rewrite her load program (see Listing 8). She has decided to use -1 to indicate that the department was not found. She also "hid" the -1 value behind a constant, so that the code is self-explanatory. All the application logic is located in the executable section, and the code is much more straightforward and easy to understand and maintain.
Code Listing 8: Calling revised ID_FOR_NAME function
PROCEDURE load_from_staging_table
IS
c_no_such_dept CONSTANT PLS_INTEGER := -1;
l_id departments.department_id%TYPE;
BEGIN
FOR dept_rec IN (SELECT * FROM dept_staging_table)
LOOP
BEGIN
l_id :=
id_for_name (dept_rec.department_name
, raise_if_ndf_in => FALSE
, ndf_value_in => c_no_such_dept
);
IF l_id = c_no_such_dept
THEN
submit_add_request (dept_rec);
ELSE
submit_update_request (dept_rec);
END IF;
END;
END LOOP;
END load_from_staging_table;
Don't expect that you can remove all the exception sections in your PL/SQL code and that it will work fine when no exceptions are raised. The existence of deliberate and unfortunate exceptions makes this impractical.
You can, however, hide these types of exceptions from your high-level application code in two ways. You can encapsulate the program that deliberately raises an exception and transform that exception into a flag or another type of data, or you can give the developer the choice of propagating unfortunate exceptions or returning a value indicating that the exception was raised.
Whichever approach you take, the most important thing is to discuss and decide these issues before you start building your next application.
Next Steps
READ more Best Practice PL/SQL
DOWNLOAD Oracle Database 11g
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.