Error Handling and Built-in Exceptions - Oracle PL/SQL by Example, Fifth Edition (2015)

Oracle PL/SQL by Example, Fifth Edition (2015)

Chapter 8. Error Handling and Built-in Exceptions

In this chapter, you will learn about

Image Handling Errors

Image 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.

Lab 8.1: Handling Errors

After this lab, you will be able to

Image Understand the Importance of Error Handling

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

For Example ch08_1a.sql

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 variablev_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:

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:

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

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

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:

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.

Watch Out!

On many occasions, a user does not have access to the code. Therefore, references to line numbers and keywords in a program are not significant to most users.

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.

Lab 8.2: Built-in Exceptions

After this lab, you will be able to

Image Use Built-in Exceptions

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

Listing 8.2 PL/SQL Block Structure

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

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 firstDBMS_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:

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:

Image 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.

Image 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.

Image 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.

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

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

Image 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_ERRORexception is raised.

Image 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

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:

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.

Did You Know?

That built-in exceptions are raised implicitly. Therefore, you need to specify only which action must be taken in the case of a particular exception.

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

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, theDBMS_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:

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

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.

Summary

In this chapter, you began exploring the concepts of error handling and built-in exceptions supported in PL/SQL. In the next two chapters, you will continue learning about exceptions, their scope and propagation, and ways to define your own exceptions. Finally, in Chapter 24, you will discover how to produce meaningful error reporting within your code with the help of Oracle’s built-in packages DBMS_UTILITY and UTL_CALLSTACK. You will also see why the UTL_CALLSTACK package introduced in Oracle 12c is a better alternative when it comes to error reporting.

By the Way

The companion website provides additional exercises and suggested answers for this chapter, with discussion related to how those answers resulted. The main purpose of these exercises is to help you test the depth of your understanding by utilizing all of the skills that you have acquired throughout this chapter.