8. Error Handling and Built-in Exceptions

In Chapter 1, you encountered two types of errors that can be found in a program: compilation errors and runtime errors. You also learned that a special section in a PL/SQL block handles runtime errors. In this so-called exception-handling section, runtime errors are referred to as exceptions. The exception-handling section allows programmers to specify which actions should be taken when a specific exception occurs.

In PL/SQL, there are two types of exceptions: built-in exceptions and user-defined exceptions. In this chapter, you will learn how to handle certain kinds of runtime errors with the help of built-in exceptions. User-defined exceptions are discussed in Chapters 9 and 10.

The following example illustrates some of the differences between compilation and runtime errors:

For Example  ch08_1a.sql

Click here to view code image

DECLARE
  v_num1   INTEGER := &sv_num1;
  v_num2   INTEGER := &sv_num2;
  v_result NUMBER;
BEGIN
  v_result = v_num1 / v_num2;
  DBMS_OUTPUT.PUT_LINE ('v_result: '||v_result);
END;

This example is a very simple program in which there are two variables, v_num1 and v_num2. A user supplies values for these variables. Next, v_num1 is divided by v_num2, and the result of this division is stored in the third variable, v_result. Finally, the value of the variable v_result is displayed on the screen.

Now, assume that a user supplies values of 3 and 5 for the variables v_num1 and v_num2, respectively. As a result, the example produces the following output:

Click here to view code image

ORA-06550: line 6, column 13:
PLS-00103: Encountered the symbol "=" when expecting one of the following:

  := . ( @ % ;
The symbol ":= was inserted before "=" to continue.

You probably noticed that the script did not execute successfully. A syntax error was encountered at line 6. Close inspection of the example shows that the statement

v_result = v_num1 / v_num2;

contains an equal sign operator where an assignment operator should be used. The statement should be rewritten as follows:

v_result := v_num1 / v_num2;

Once the corrected example is run again, the following output is produced:

v_result: .6

The example now executes successfully because the syntax error has been corrected.

Next, if you change the values of the variables v_num1 and v_num2 to 4 and 0, respectively, the following output is produced:

Click here to view code image

ORA-01476: divisor is equal to zero
ORA-06512: at line 6
01476. 00000 -  "divisor is equal to zero"

Even though this example does not contain syntax errors, the script terminated prematurely because the value entered for v_num2, the divisor, was 0. Division by 0 is undefined, so this operation leads to an error.

This example illustrates a runtime error that cannot be detected by the compiler. In other words, for some of the values entered for the variables v_num1 and v_num2, this example executes successfully. When other values are entered for v_num1 and v_num2, this example cannot execute. As a result, a runtime error occurs. Recall that the compiler cannot detect runtime errors. In this case, a runtime error occurs because the compiler does not know the result of the division of v_num1 by v_num2. This result can be determined only at run time—hence, this error is referred to as a runtime error.

To handle this type of error in the program, an exception handler must be added. The exception-handling section has the structure shown in Listing 8.1.

Listing 8.1 Exception-Handling Section

Click here to view code image

EXCEPTION
  WHEN EXCEPTION_NAME
  THEN
    ERROR-PROCESSING STATEMENTS;

Note that the exception-handling section appears after the executable section of the block. Therefore, the preceding example can be rewritten in the following manner (newly added statements are shown in bold):

For Example  ch08_1b.sql

Click here to view code image

DECLARE
  v_num1   INTEGER := &sv_num1;
  v_num2   INTEGER := &sv_num2;
  v_result NUMBER;
BEGIN
  v_result := v_num1 / v_num2;
  DBMS_OUTPUT.PUT_LINE ('v_result: '||v_result);
EXCEPTION
  WHEN ZERO_DIVIDE
  THEN
    DBMS_OUTPUT.PUT_LINE ('A number cannot be divided by zero.'),
END;

The section of the example in bold shows the exception-handling section of the block. When this version of the example is executed with values of 4 and 0 for variables v_num1 and v_num2, respectively, the following output is produced:

Click here to view code image

A number cannot be divided by zero.

This output shows that once an attempt to divide v_num1 by v_num2 was made, the exception-handling section of the block was executed. Therefore, the error message specified by the exception-handling section was displayed on the screen.

This version of the output illustrates several of the advantages that arise from use of an exception-handling section. You probably noticed that the output looks cleaner compared to the previous version. Even though the error message is still displayed on the screen, the output is more informative. In short, it is oriented more toward a user than a programmer.

An exception-handling section allows a program to execute to completion, instead of terminating prematurely. It also provides for isolation of error-handling routines. In other words, all error-processing code for a specific block can be placed within a single section. As a result, the logic of the program becomes easier to follow and understand. Finally, adding an exception-handling section enables event-driven processing of errors. As in the example shown earlier, when a specific exception event occurs, such as division by 0, the exception-handling section executes, and the error message specified by the DBMS_OUTPUT.PUT_LINE statement is displayed on the screen.

As mentioned earlier, a PL/SQL block has the structure shown in Listing 8.2.

Listing 8.2 PL/SQL Block Structure

Click here to view code image

DECLARE
  ...
BEGIN
  EXECUTABLE STATEMENTS;
EXCEPTION
  WHEN EXCEPTION_NAME
  THEN
    ERROR-PROCESSING STATEMENTS;
END;

When an error occurs that raises a built-in exception, the exception is said to be raised implicitly. In other words, if a program breaks an Oracle rule, control passes to the exception-handling section of the block. At this point, the error-processing statements are executed. After the exception-handling section of the block has executed, the block terminates; that is, control does not return to the executable section of the block. The following example illustrates this point:

For Example  ch08_2a.sql

Click here to view code image

DECLARE
  v_student_name VARCHAR2(50);
BEGIN
  SELECT first_name||' '||last_name
    INTO v_student_name
    FROM student
   WHERE student_id = 101;

   DBMS_OUTPUT.PUT_LINE ('Student name is '||v_student_name);
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    DBMS_OUTPUT.PUT_LINE ('There is no such student'),
END;

This example produces the following output:

There is no such student

Because there is no record in the STUDENT table with student ID 101, the SELECT INTO statement does not return any rows. As a result, control passes to the exception-handling section of the block, and the error message “There is no such student” is displayed on the screen. Even though a DBMS_OUTPUT.PUT_LINE statement appears right after the SELECT INTO statement, it will not be executed because control has been transferred to the exception-handling section. Control will never return to the executable section of this block, which contains the first DBMS_OUTPUT.PUT_LINE statement.

While every Oracle runtime error has a number associated with it, it must be handled by its name in the exception-handling section. One of the outputs from the example used in the previous lab of this chapter included the following error message:

Click here to view code image

ORA-01476: divisor is equal to zero

where ORA-01476 stands for the error number. This error number refers to the error named ZERO_DIVIDE. Some common Oracle runtime errors are predefined in PL/SQL as exceptions. The following list identifies some of these predefined exceptions and explains how they are raised:

NO_DATA_FOUND: This exception is raised when a SELECT INTO statement that makes no calls to group functions, such as SUM or COUNT, does not return any rows. For example, suppose you issue a SELECT INTO statement against the STUDENT table where the student ID equals 101. If no record in the STUDENT table meets this criterion (student ID equals 101), the NO_DATA_FOUND exception is raised.

When a SELECT INTO statement calls a group function, such as COUNT, the result set is never empty. When used in a SELECT INTO statement against the STUDENT table, function COUNT will return 0 for the value of student ID 123. Hence, a SELECT INTO statement that calls a group function will never raise the NO_DATA_FOUND exception.

TOO_MANY_ROWS: This exception is raised when a SELECT INTO statement returns more than one row. By definition, a SELECT INTO can return only a single row. If a SELECT INTO statement returns more than one row, the definition of the SELECT INTO statement is violated. This causes the TOO_MANY_ROWS exception to be raised.

For example, you issue a SELECT INTO statement against the STUDENT table for a specific ZIP code. It is highly likely that this SELECT INTO statement will return more than one row, because many students may live in the same ZIP code area.

ZERO_DIVIDE: This exception is raised when a division operation is performed in the program and a divisor is equal to zero. An example in the previous lab of this chapter illustrates how this exception is raised.

LOGIN_DENIED: This exception is raised when a user is trying to log in to Oracle with an invalid username or password.

PROGRAM_ERROR: This exception is raised when a PL/SQL program has an internal problem.

VALUE_ERROR: This exception is raised when a conversion or size mismatch error occurs. For example, suppose you select a student’s last name into a variable that has been defined as VARCHAR2(5). If the student’s last name contains more than five characters, the VALUE_ERROR exception is raised.

DUP_VALUE_ON_INDEX: This exception is raised when a program tries to store a duplicate value in a column or columns that have a unique index defined on them. For example, suppose you are trying to insert a record into the SECTION table for the course number 25, section 1. If a record for the given course and section number already exists in the SECTION table, the DUP_VAL_ON_INDEX exception is raised because these columns have a unique index defined on them.

So far, you have seen examples of programs that are able to handle a single exception only. For example, a PL/SQL block contains an exception handler with a single exception ZERO_DIVIDE. However, many times you need to handle different exceptions in the PL/SQL block. Moreover, often you need to specify different actions that must be taken when a particular exception is raised, as the following example illustrates:

For Example  ch08_3a.sql

Click here to view code image

DECLARE
  v_student_id NUMBER      := &sv_student_id;
  v_enrolled   VARCHAR2(3) := 'NO';
BEGIN
  DBMS_OUTPUT.PUT_LINE ('Check if the student is enrolled'),
  SELECT 'YES'
    INTO  v_enrolled
    FROM  enrollment
   WHERE  student_id = v_student_id;

   DBMS_OUTPUT.PUT_LINE ('The student is enrolled into one course'),
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    DBMS_OUTPUT.PUT_LINE ('The student is not enrolled'),

  WHEN TOO_MANY_ROWS
  THEN
     DBMS_OUTPUT.PUT_LINE ('The student is enrolled in multiple courses'),
END;

This example contains two exceptions in a single exception-handling section. The first exception, NO_DATA_FOUND, will be raised if there are no records in the ENROLLMENT table for a particular student. The second exception, TOO_MANY_ROWS, will be raised if a particular student is enrolled in more than one course.

Consider what happens if you run this example for three different values of student ID: 102, 103, and 319. In the first run, when the student ID is 102, the example produces the following output:

Click here to view code image

Check if the student is enrolled
The student is enrolled in multiple courses

In this case, the first DBMS_OUTPUT.PUT_LINE statement is executed, and the message “Check if the . . .” is displayed on the screen. Then the SELECT INTO statement is executed. You have probably noticed that the DBMS_OUTPUT.PUT_LINE statement following the SELECT INTO statement was not executed. When the SELECT INTO statement is executed for student ID 102, multiple rows are returned. Because the SELECT INTO statement can return only a single row, control is passed to the exception-handling section of the block. Next, the PL/SQL block raises the proper exception. As a result, the message “The student is enrolled in multiple courses” is displayed on the screen; this message is specified by the exception TOO_MANY_ROWS.

In the second run, when the student ID is 103, the example produces different output:

Click here to view code image

Check if the student is enrolled
The student is enrolled into one course

For this run, the first DBMS_OUTPUT.PUT_LINE statement is executed, and the message “Check if the . . .” is displayed on the screen. Then the SELECT INTO statement is executed. When the SELECT INTO statement is executed for student ID 103, a single row is returned. Next, the DBMS_OUTPUT.PUT_LINE statement following the SELECT INTO statement is executed. As a result, the message “The student is enrolled into one course” is displayed on the screen. Notice that for this value of the variable v_student_id, no exception has been raised.

In the third run, when the student ID is 319, the example produces the following output:

Click here to view code image

Check if the student is enrolled
The student is not enrolled

Just as in the previous runs, the first DBMS_OUTPUT.PUT_LINE statement is executed, and the message “Check if the . . .” is displayed on the screen. Then the SELECT INTO statement is executed. When the SELECT INTO statement is executed for student ID 319, no rows are returned. As a result, control passes to the exception-handling section of the PL/SQL block, and the proper exception is raised. In this case, the NO_DATA_FOUND exception is raised because the SELECT INTO statement failed to return a single row. Thus, the message “The student is not enrolled” is displayed on the screen.

So far, you have seen examples of exception-handling sections that have particular exceptions, such as NO_DATA_FOUND and ZERO_DIVIDE. However, you cannot always predict beforehand which exception might be raised by a PL/SQL block. In cases like this, a special exception handler called OTHERS is used. All predefined Oracle errors (exceptions) can be handled with the use of the OTHERS handler.

Consider the following example:

For Example  ch08_4a.sql

Click here to view code image

DECLARE
  v_instructor_id   NUMBER := &sv_instructor_id;
  v_instructor_name VARCHAR2(50);
BEGIN
  SELECT first_name||' '||last_name
    INTO v_instructor_name
    FROM instructor
   WHERE instructor_id = v_instructor_id;

   DBMS_OUTPUT.PUT_LINE ('Instructor name is '||v_instructor_name);
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.PUT_LINE ('An error has occurred'),
END;

When a value of 100 is provided at run time for the variable v_instructor_id, this example produces the following output:

An error has occurred

This example demonstrates not only the use of the OTHERS exception handler, but also a bad programming practice. The exception OTHERS has been raised because there is no record in the INSTRUCTOR table for instructor ID 100.

This is a simple example, where it is possible to guess which exception handlers should be used. In many instances, however, you may find a number of programs that have been written with a single exception handler, OTHERS. This is a bad programming practice, because such use of this exception handler does not give you or your user detailed feedback. You do not really know which error has occurred, and your user does not know whether he or she entered some information incorrectly. Other special error-reporting functions, SQLCODE and SQLERRM, are very useful when used with the OTHERS handler that provide more details. You will learn about them in Chapter 10.