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

Chapter 9. Exceptions


In this chapter, you will learn about

Image Exception Scope

Image User-Defined Exceptions

Image Exception Propagation


In Chapter 8, you explored the concept of error handling and built-in exceptions. In this chapter you will continue that exploration by examining whether an exception can catch a runtime error occurring in the declaration, executable, or exception-handling section of a PL/SQL block. You will also learn how to define your own exceptions and how to re-raise an exception.

Lab 9.1: Exception Scope


After this lab, you will be able to

Image Understand the Scope of an Exception


You are already familiar with the term scope—for example, the scope of a variable. Even though variables and exceptions serve different purposes, the same scope rules apply to them. These rules are best illustrated by means of an example.

For Example  ch09_1a.sql

DECLARE
  v_student_id NUMBER := &sv_student_id;
  v_name       VARCHAR2(30);
BEGIN
  SELECT RTRIM(first_name)||' '||RTRIM(last_name)
    INTO v_name
    FROM student
   WHERE student_id = v_student_id;

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

In this example, student’s name is displayed on the screen for a given value of student ID provided at run time. If there is no record in the STUDENT table corresponding to the value of v_student_id, the exception NO_DATA_FOUND is raised. Therefore, you can say that the exceptionNO_DATA_FOUND covers this block, or that this block is the scope of this exception. In other words, the scope of an exception is the portion of the block that is covered by this exception.

Now, you can expand on that understanding (newly added statements are shown in bold):

For Example  ch09_1b.sql

<<outer_block>>
DECLARE
  v_student_id NUMBER := &sv_student_id;
  v_name       VARCHAR2(30);
  v_total      NUMBER(1);

BEGIN
  SELECT RTRIM(first_name)||' '||RTRIM(last_name)
    INTO v_name
    FROM student
   WHERE student_id = v_student_id;

  DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name);

  <<inner_block>>
  BEGIN
    SELECT COUNT(*)
      INTO v_total
      FROM enrollment
     WHERE student_id = v_student_id;

    DBMS_OUTPUT.PUT_LINE ('Student is registered for '||v_total||' course(s)');
  EXCEPTION
    WHEN VALUE_ERROR OR INVALID_NUMBER
    THEN
      DBMS_OUTPUT.PUT_LINE ('An error has occurred');
  END;
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    DBMS_OUTPUT.PUT_LINE ('There is no such student');
END;

The new version of the example includes an inner block. This block has a structure similar to the outer block; that is, it has a SELECT INTO statement and an exception section to handle errors. When a VALUE_ERROR or INVALID_NUMBER error occurs in the inner block, the exception is raised.

Notice that the exceptions VALUE_ERROR and INVALID_NUMBER have been defined for the inner block only. Therefore, they can be handled only if they are raised in the inner block. If one of these errors occurs in the outer block, the program will be unable to terminate successfully.

In contrast, the exception NO_DATA_FOUND has been defined in the outer block; therefore, it is global to the inner block. However, this version of the example never raises the exception NO_DATA_FOUND in the inner block. Why do you think this is the case?


Did You Know?

If you define an exception in a block, it is local to that block. However, it is global to any blocks enclosed by that block. In other words, in the case of nested blocks, any exception defined in the outer block becomes global to its inner blocks.


Note what happens when the example is changed so that the exception NO_DATA_FOUND can be raised by the inner block (all changes are shown in bold).

For Example  ch09_1c.sql

<<outer_block>>
DECLARE
  v_student_id NUMBER := &sv_student_id;
  v_name       VARCHAR2(30);
  v_registered CHAR;

BEGIN
  SELECT RTRIM(first_name)||' '||RTRIM(last_name)
    INTO v_name
    FROM student
   WHERE student_id = v_student_id;

  DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name);

  <<inner_block>>
  BEGIN
    SELECT 'Y'
      INTO v_registered
      FROM enrollment
     WHERE student_id = v_student_id;
    DBMS_OUTPUT.PUT_LINE ('Student is registered');
  EXCEPTION
    WHEN VALUE_ERROR OR INVALID_NUMBER
    THEN
      DBMS_OUTPUT.PUT_LINE ('An error has occurred');
  END;

EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    DBMS_OUTPUT.PUT_LINE ('There is no such student');
END;

The new version of the example has a different SELECT INTO statement. To answer the question posed earlier, the exception NO_DATA_FOUND can be raised by the inner block because the SELECT INTO statement does not contain a group function, COUNT(). This function always returns a result, so when no rows are returned by the SELECT INTO statement, the value returned by the COUNT(*) equals zero.

Now, consider the output produced by this example when a value of 284 is provided for the student ID:

Student name is Salewa Lindeman
There is no such student

You have probably noticed that this example produces only a partial output. Even though you are able to see the student’s name, the error message is displayed, indicating that this student does not exist. This error message is displayed because the exception NO_DATA_FOUND is raised in the inner block.

The SELECT INTO statement of the outer block returns the student’s name, which is then displayed on the screen by the first DBMS_OUTPUT.PUT_LINE statement. Next, control passes to the inner block. The SELECT INTO statement of the inner block does not return any rows. As a result, an error occurs and the NO_DATA_FOUND exception is raised.

Next, PL/SQL tries to find a handler for the NO_DATA_FOUND exception in the inner block. Because there is no such handler in the inner block, control is transferred to the exception section of the outer block. The exception section of the outer block contains the handler for the exceptionNO_DATA_FOUND. Consequently, this handler executes, and the message “There is no such student” is displayed on the screen. The process, which is called exception propagation, is discussed in detail in Lab 9.3.

Be aware that this example has been provided for illustrative purposes only. In its current version, it is not very useful. The SELECT INTO statement of the inner block is prone to another exception, TOO_MANY_ROWS, which is not handled by this example. In addition, the error message “There is no such student” is not very descriptive when the exception NO_DATA_FOUND is raised by the inner block.

Lab 9.2: User-Defined Exceptions


After this lab, you will be able to

Image Use User-Defined Exceptions


Often in your programs, you may need to handle problems that are specific to the program you write. For example, suppose your program asks a user to enter a value for the student ID. This value is then assigned to the variable v_student_id, which is used later in the program. Generally, you want a positive number for an ID. By mistake, however, the user enters a negative number. However, no error has occurred because the variable v_student_id has been defined as a number, and the user has supplied a legitimate numeric value. Therefore, you may want to implement your own exception to handle this situation.

This type of an exception is called a user-defined exception because it is defined by the programmer. As a result, before such an exception can be used, it must be declared. A user-defined exception is declared in the declarative part of a PL/SQL block, as shown in Listing 9.1.

Listing 9.1 User-Defined Exception Declaration

DECLARE
  exception_name EXCEPTION;

Notice that this declaration looks similar to a variable declaration. That is, you specify an exception name followed by the keyword EXCEPTION.

Consider the following code fragment:

For Example

DECLARE
  e_invalid_id EXCEPTION;

In this code fragment, the name of the exception is prefixed by the letter “e.” This is not a required syntax; rather, it allows you to differentiate between variable names and exception names.

Once an exception has been declared, the executable statements associated with that exception are specified in the exception-handling section of the block. The format of the exception-handling section is the same as for built-in exceptions. Consider the following code fragment:

For Example

DECLARE
  e_invalid_id EXCEPTION;
BEGIN
  ...
EXCEPTION
  WHEN e_invalid_id
  THEN
    DBMS_OUTPUT.PUT_LINE ('An ID cannot be negative');
END;

You already know that built-in exceptions are raised implicitly. In other words, when a certain error occurs, a built-in exception associated with this error is raised. Of course, you are assuming that you have included this exception in the exception-handling section of your program. For example, a TOO_MANY_ROWS exception is raised when a SELECT INTO statement returns multiple rows.

A user-defined exception must be raised explicitly. In other words, you need to specify in your program under which circumstances an exception must be raised, as shown in Listing 9.2.

Listing 9.2 Raising a User-Defined Exception

DECLARE
  exception_name EXCEPTION;
BEGIN
  ...
  IF CONDITION
     THEN
    RAISE exception_name;
  END IF;
  ...
EXCEPTION
  WHEN exception_name
  THEN
    ERROR-PROCESSING STATEMENTS;
END;

In this structure, the circumstances under which a user-defined exception must be raised are determined with the help of the IF statement. If CONDITION evaluates to TRUE, a user-defined exception is raised with the help of the RAISE statement. If CONDITION evaluates to FALSE, the program proceeds with its normal execution. In other words, the statements following the IF statement are executed. Note that any form of the IF statement can be used to check when a user-defined exception must be raised.

In the next example, which is based on the code fragments provided earlier in this lab, you will see that the exception e_invalid_id is raised when the user enters a negative number for the variable v_student_id.

For Example  ch09_2a.sql

DECLARE
  v_student_id    STUDENT.STUDENT_ID%TYPE := &sv_student_id;
  v_total_courses NUMBER;
  e_invalid_id    EXCEPTION;
BEGIN
  IF v_student_id < 0
  THEN
    RAISE e_invalid_id;
  END IF;

  SELECT COUNT(*)
    INTO v_total_courses
    FROM enrollment
   WHERE student_id = v_student_id;

  DBMS_OUTPUT.PUT_LINE ('The student is registered for '||v_total_courses||' courses');
  DBMS_OUTPUT.PUT_LINE ('No exception has been raised');
EXCEPTION
  WHEN e_invalid_id
  THEN
    DBMS_OUTPUT.PUT_LINE ('An ID cannot be negative');
END;

In this example, the exception e_invalid_id is raised with the help of the IF statement. Once a value is supplied for the variable v_student_id, the sign of this numeric value is checked. If the value is less than zero, the IF statement evaluates to TRUE and the exceptione_invalid_id is raised. In turn, control passes to the exception-handling section of the block. Next, statements associated with this exception are executed. In this case, the message “An ID cannot be negative” is displayed on the screen. If the value entered for the v_student_id is positive, the IF statement yields FALSE and the rest of the statements in the body of the block are executed.

Consider executing this example for two values of v_student_id, 102 and –102. The first run of the example (the student ID is 102) produces the following output:

The student is registered for 2 courses
No exception has been raised

For this run, the user provides a positive value for the variable v_student_id. As a result, the IF statement evaluates to FALSE, and the SELECT INTO statement determines how many records are in the ENROLLMENT table for the given student ID. Next, the messages “The student is registered for 2 courses” and “No exception has been raised” are displayed on the screen. At this point, the body of the PL/SQL block has executed to completion.

A second run of the example (the student ID is –102) produces the following output:

An ID cannot be negative

For this run, the user entered a negative value for the variable v_student_id. The IF statement evaluates to TRUE and the exception e_invalid_id is raised. As a result, control of the execution passes to the exception-handling section of the block, and the message “An ID cannot be negative” is displayed on the screen.


Watch Out!

The RAISE statement should be used in conjunction with an IF statement. Otherwise, control of the execution will be transferred to the exception-handling section of the block for every single execution. Consider the following example:

DECLARE
  e_test_exception EXCEPTION;
BEGIN
  DBMS_OUTPUT.PUT_LINE ('Exception has not been raised');
  RAISE e_test_exception;
  DBMS_OUTPUT.PUT_LINE ('Exception has been raised');
EXCEPTION
  WHEN e_test_exception
  THEN
    DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

Every time this example is run, the following output is produced:

Exception has not been raised
An error has occurred

Even though no error has occurred, control is transferred to the exception-handling section. It is important for you to check whether the error has occurred before raising the exception associated with that error.


The same scope rules apply to user-defined exceptions as apply to built-in exceptions. An exception declared in the inner block must be raised in the inner block and defined in the exception-handling section of the inner block. Consider the following example:

For Example  ch09_3a.sql

<<outer_block>>
BEGIN
  DBMS_OUTPUT.PUT_LINE ('Outer block');
  <<inner_block>>
  DECLARE
    e_my_exception EXCEPTION;
  BEGIN
    DBMS_OUTPUT.PUT_LINE ('Inner block');
  EXCEPTION
    WHEN e_my_exception
    THEN
      DBMS_OUTPUT.PUT_LINE ('An error has occurred');
  END;

  IF 10 > &sv_number
  THEN
    RAISE e_my_exception;
  END IF;
END;

In this example, the exception, e_my_exception, has been declared in the inner block. However, you are trying to raise this exception in the outer block. This example causes a syntax error because the exception declared in the inner block ceases to exist once the inner block terminates. As a result, this example produces the following output when a value of 11 is provided at run time:

ORA-06550: line 19, column 13:
PLS-00201: identifier 'E_MY_EXCEPTION' must be declared
ORA-06550: line 19, column 7:
PL/SQL: Statement ignored

Notice that the error message

PLS-00201: identifier 'E_MY_EXCEPTION' must be declared

is the same error message you get when you try to use a variable that has not been declared.

Lab 9.3: Exception Propagation


After this lab, you will be able to

Image Understand How Exceptions Propagate

Image Re-raise Exceptions


You already have seen how different types of exceptions are raised when a runtime error occurs in the executable portion of the PL/SQL block. However, a runtime error may also occur in the declaration section of the block or in the exception-handling section of the block. The rules that govern how exceptions are raised in these situations are referred to as exception propagation.

Consider the first case, in which a runtime error occurs in the executable section of the PL/SQL block. This case should be treated as a review because the examples given earlier in this chapter show how an exception is raised when an error occurs in the executable section of the block.

If a specific exception is associated with a particular error, control passes to the exception-handling section of the block. Once the statements associated with the exception are executed, control passes to the host environment or to the enclosing block. If there is no exception handler for this error, the exception is propagated to the enclosing block (outer block). The steps just described are then repeated. If no exception handler is found, the execution of the program halts, and control is transferred to the host environment.

Next, consider the second case, in which a runtime error occurs in the declaration section of the block. If there is no outer block, the execution of the program halts, and control passes to the host environment. Consider the following example:

For Example  ch09_4a.sql

DECLARE
  v_test_var CHAR(3):= 'ABCDE';
BEGIN
  DBMS_OUTPUT.PUT_LINE ('This is a test');
EXCEPTION
  WHEN INVALID_NUMBER OR VALUE_ERROR
  THEN
    DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

When executed, this example produces the following output:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 2

In this example, the assignment statement in the declaration section of the block causes an error. Even though an exception handler for this error exists, the block is not able to execute successfully. Based on this example, you can conclude that when a runtime error occurs in the declaration section of the PL/SQL block, the exception-handling section of this block is not able to catch the error.

Next, consider a modified version of the same example that employs nested PL/SQL blocks (changes are shown in bold).

For Example  ch09_4b.sql

<<outer_block>>
BEGIN
  <<inner_block>>
  DECLARE
    v_test_var CHAR(3):= 'ABCDE';
  BEGIN
    DBMS_OUTPUT.PUT_LINE ('This is a test');
  EXCEPTION
    WHEN INVALID_NUMBER OR VALUE_ERROR
    THEN
      DBMS_OUTPUT.PUT_LINE ('An error has occurred in the inner block');
  END;
EXCEPTION
  WHEN INVALID_NUMBER OR VALUE_ERROR
  THEN
    DBMS_OUTPUT.PUT_LINE ('An error has occurred in the program');
END;

When executed, this example produces the following output:

An error has occurred in the program

In this version of the example, the PL/SQL block is enclosed by another block, and the program is able to complete. In this case, the exception defined in the outer block is raised when the error occurs in the declaration section of the inner block. Therefore, you can conclude that when a runtime error occurs in the declaration section of the inner block, the exception immediately propagates to the enclosing (outer) block.

Finally, consider a third case, in which a runtime error occurs in the exception-handling section of the block. Just as in the previous case, if there is no outer block, the execution of the program halts and control passes to the host environment. Consider the following example:

For Example  ch09_5a.sql

DECLARE
  v_test_var CHAR(3) := 'ABC';
BEGIN
  v_test_var := '1234';
  DBMS_OUTPUT.PUT_LINE ('v_test_var: '||v_test_var);
EXCEPTION
  WHEN INVALID_NUMBER OR VALUE_ERROR
  THEN
    v_test_var := 'ABCD';
    DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

When executed, this example produces the following output:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 9
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

As you can see, the assignment statement in the executable section of the block causes an error. In turn, control is transferred to the exception-handling section of the block. However, the assignment statement in the exception-handling section of the block raises the same error. As a result, the output of this example displays the same error message twice. The first message is generated by the assignment statement in the executable section of the block, and the second message is generated by the assignment statement of the exception-handling section of this block. Based on this example, you can conclude that when a runtime error occurs in the exception-handling section of the PL/SQL block, the exception-handling section of this block is not able to prevent the error.

Next, consider a modified version of the same example with nested PL/SQL blocks (affected statements are shown in bold):

For Example  ch09_5b.sql

<<outer_block>>
BEGIN
  <<inner_block>>
  DECLARE
    v_test_var CHAR(3) := 'ABC';
  BEGIN
    v_test_var := '1234';
    DBMS_OUTPUT.PUT_LINE ('v_test_var: '||v_test_var);
  EXCEPTION
    WHEN INVALID_NUMBER OR VALUE_ERROR
    THEN
      v_test_var := 'ABCD';
      DBMS_OUTPUT.PUT_LINE ('An error has occurred in the inner block');
  END;
EXCEPTION
  WHEN INVALID_NUMBER OR VALUE_ERROR
  THEN
    DBMS_OUTPUT.PUT_LINE ('An error has occurred in the program');
END;

When executed, this version produces the following output:

An error has occurred in the program

In this version of the example, the PL/SQL block is enclosed by another block, and the program is able to complete. In this case, the exception defined in the outer block is raised when the error occurs in the exception-handling section of the inner block. Therefore, you can conclude thatwhen a runtime error occurs in the exception-handling section of the inner block, the exception immediately propagates to the enclosing block.

In the previous two examples, an exception was raised implicitly by a runtime error in the exception-handling section of the block. However, an exception can also be raised explicitly in the exception-handling section of the block by the RAISE statement. Consider the following example:

For Example  ch09_6a.sql

<<outer_block>>
DECLARE
  e_exception1 EXCEPTION;
  e_exception2 EXCEPTION;
BEGIN
  <<inner_block>>
  BEGIN
    RAISE e_exception1;
  EXCEPTION
    WHEN e_exception1
    THEN
      RAISE e_exception2;
    WHEN e_exception2
    THEN
      DBMS_OUTPUT.PUT_LINE ('An error has occurred in the inner block');
  END;
EXCEPTION
  WHEN e_exception2
  THEN
    DBMS_OUTPUT.PUT_LINE ('An error has occurred in the program');
END;

This example produces the following output:

An error has occurred in the program

The declaration portion of the block contains declarations of two exceptions, e_exception1 and e_exception2. The exception e_exception1 is raised in the inner block via the RAISE statement. In the exception-handling section of the inner block, the exceptione_exception1 tries to raise e_exception2. Even though an exception handler for e_exception2 exists in the inner block, control is still transferred to the outer block. This happens because only one exception can be raised in the exception-handling section of the block. Only after one exception has been handled can another be raised, but two or more exceptions cannot be raised simultaneously. This flow of execution is illustrated in Figure 9.1.

Image

Figure 9.1 Flow of the Execution for Example ch09_6a.sql

Essentially, when the exception e_exception2 is raised in the exception-handling section of the inner block, it cannot be handled in the same exception-handling section. Thus, the portion of the code surrounded by rectangular brackets never executes. Instead, control passes to the exception-handling section of the outer block and the message “An error has occurred in the program” is displayed on the screen.


Watch Out!

When an exception is raised in a PL/SQL block that does not have an appropriate exception-handling mechanism and is not enclosed by another block, control is transferred to the host environment, and the program is not able to complete successfully. The following code fragment illustrates this case:

DECLARE
  e_exception1 EXCEPTION;
BEGIN
  RAISE e_exception1;
END;

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 4

Note that this behavior applies to built-in exceptions and was also seen in Chapter 8.


Re-raising Exceptions

On some occasions you may want to be able to stop your program if a certain type of error occurs. In other words, you may want to handle an exception in the inner block and then pass it to the outer block. This process is called re-raising an exception. The following example helps to illustrate this point:

For Example  ch09_7a.sql

<<outer_block>>
DECLARE
  e_exception EXCEPTION;
BEGIN
  <<inner_block>>
  BEGIN
    RAISE e_exception;
  EXCEPTION
    WHEN e_exception
    THEN
      RAISE;
  END;
EXCEPTION
  WHEN e_exception
  THEN
    DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

In this example, the exception e_exception is first declared in the outer block, then raised in the inner block. As a result, control is transferred to the exception-handling section of the inner block. The RAISE statement in the exception-handling section of the block causes the exception to propagate to the exception-handling section of the outer block. Notice that when the RAISE statement is used in the exception-handling section of the inner block, it is not followed by the exception name.

When run, this example produces the following output:

An error has occurred


Watch Out!

When an exception is re-raised in the block that is not enclosed by any other block, the program is unable to complete successfully. Consider the following example:

DECLARE
  e_exception EXCEPTION;
BEGIN
  RAISE e_exception;
EXCEPTION
  WHEN e_exception
  THEN
    RAISE;
END;

When run, this example produces the following output:

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 7


Summary

In this chapter, you learned about exception scope and propagation, and saw how to define and raise your own exceptions. In addition, you learned how to re-raise an exception. In the next chapter, you will discover how to produce meaningful error reporting within your code with the help of Oracle’s built-in functions, SQLCODE and SQLERRM.


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.