RSS

8.1 Exception Scope

26 Apr

DECLARE

v_student_id NUMBER := &sv_studnet_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;

You can say that the exception NO_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.

DECLARE

v_student_id NUMBER := &sv_studnet_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 occured’);

END;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(‘There is no such student’);

END;

 

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 handled only if they are raised in the inner block. If one of these errors occurs in the outer block, the program is 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. 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.

 

DECLARE

v_student_id NUMBER := &sv_studnet_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 occured’);

END;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(‘There is no such student’);

END;

 

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.

 

Because no student_id = 284, so can retrieve a record, so outer exception NO_DATA_FOUND will be raised.

Advertisements
 
Leave a comment

Posted by on 04/26/2010 in ORACLE

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: