Lab 10.1 Exception Scope | Oracle PL[s]SQL by Example

Lab Objective

After this Lab, you will be able to:

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. Now examine the scope of an exception by means of an example.

FOR EXAMPLE

 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, you display the student's name on the screen. If there is no record in the STUDENT table corresponding to the value of v_student_id provided by the user , the exception NO_DATA_FOUND is raised. Therefore, you can say that the exception NO_DATA_FOUND covers this block, or 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:

FOR EXAMPLE

 DECLARE    v_student_id NUMBER := &sv_student_id;    v_name VARCHAR2(30);  v_total NUMBER(1);  -- outer block 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 part of the example shown in bold letters has been added to the original version of the example. The new version of the example has an inner block added to it. This block has a structure similar to the outer block. It has a SELECT INTO statement and an exception section to handle errors. When a VALUE_ERROR or an INVALID_NUMBER error occurs in the inner block, the exception is raised.

It is important that you realize that the exceptions VALUE_ERROR and INVALID_ NUMBER have been defined for the inner block only. Therefore, they can be raised in the inner block only. If one of these errors occurs in the outer block, this program will be unable to terminate successfully.

On the other hand, the exception NO_DATA_FOUND has been defined in the outer block; therefore, it is global to the inner block. This version of the example will never raise the exception NO_DATA_FOUND in the inner block. Why do you think this is the case?

It is important to note that 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.

FOR EXAMPLE

 DECLARE    v_student_id NUMBER := &sv_student_id;    v_name VARCHAR2(30);  v_registered CHAR;  -- outer block 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 part of the example shown in bold letters has been added to the original version of the example. 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, run this example with the value of 284 for student ID. As a result, the following output is produced:

  Enter value for sv_student_id: 284   old   2:    v_student_id NUMBER := &sv_student_id;   new   2:    v_student_id NUMBER := 284;   Student name is Salewa Lindeman   There is no such student   PL/SQL procedure successfully completed.

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 saying 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, and it is displayed on the screen by the DBMS_OUTPUT.PUT_LINE statement. Next, control is passed to the inner block. The SELECT INTO statement of the inner block does not return any rows. As a result, the error occurs.

Next, PL/SQL tries to find a handler for the exception NO_DATA_FOUND 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 exception NO_DATA_FOUND. So this handler executes, and the message "There is no such student" is displayed on the screen. The process is called exception propagation, and it will be discussed in detail in Lab 10.3.

It is important to realize that this example has been shown 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, that 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.