As Published In
Oracle Magazine
July/August 2008

DEVELOPER: PL/SQL Practices


On Exceptions and Rules

By Steven Feuerstein Oracle ACE Director

Best practices for where, when, and how to handle exceptions

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
  THEN
    DBMS_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 time—and 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 confusion—and 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;
        
  BEGIN
    l_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. If you agree, I urge you to visit www.ILovePLSQLAnd.net and send a note to Bryn Llewellyn, Oracle's PL/SQL product manager, expressing your interest in this enhancement.

Application Source Code Inside an Exception Handler

I 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, and—assuming no errors—our 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 function—and, 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.

Next Steps



 READ more Best Practice PL/SQL

 DOWNLOAD Oracle Database 11g

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.

 


Steven Feuerstein (steven.feuerstein@quest.com) is Quest Software's PL/SQL evangelist. He has published 10 books on Oracle's programming language, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media). Feuerstein's self-appointed mission in life these days is to improve the quality and quantity of PL/SQL code testing.

Send us your comments