Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 10.3 Exercise Answers

This section gives you some suggested answers to the questions in Lab 10.3, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.

10.3.1 Answers

The variable v_my_name is declared as VARCHAR2(15). However, the value that is assigned to this variable contains seventeen letters. As a result, the assignment statement causes a runtime error.

The new version of this script produces the following output:

Enter value for sv_your_name: TEST A NAME

old 9: v_your_name := '&sv_your_name';

new 9: v_your_name := 'TEST A NAME';

Error in the outer block

This name is too long

PL/SQL procedure successfully completed.

In this version of the example, the assignment statement was moved from the executable section of the inner block to the declaration section of this block. As a result, if an exception is raised by the assignment statement of the inner block, control is transferred to the exception section of the outer block.

You can modify this example in a different manner that allows you to achieve the same result.

-- ch10_3d.sql, version 4.0
SET SERVEROUTPUT ON
DECLARE

v_my_name VARCHAR2(15) := 'ELENA';
BEGIN
DBMS_OUTPUT.PUT_LINE ('My name is '||v_my_name);
DECLARE
v_your_name VARCHAR2(15);
BEGIN

v_your_name := '&sv_your_name';
DBMS_OUTPUT.PUT_LINE ('Your name is '||v_your_name);
EXCEPTION
WHEN VALUE_ERROR THEN

RAISE;
END;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('Error in the outer block');
DBMS_OUTPUT.PUT_LINE ('This name is too long');
END;

In this version of the example, the RAISE statement was used in the exception-handling section of the inner block. As a result, the exception is re-raised in the outer block.

Both versions of this example produce very similar output. The first output is generated by the third version of the example, and the second output is generated by the fourth version of the example.

Enter value for sv_your_name: THIS NAME MUST BE REALLY LONG

old 6: v_your_name VARCHAR2(15) := '&sv_your_name';

new 6: v_your_name VARCHAR2(15) := 'THIS NAME MUST BE REALLY LONG';

My name is ELENA

Error in the outer block

This name is too long

PL/SQL procedure successfully completed.

Enter value for sv_your_name: THIS NAME MUST BE REALLY LONG

old 8: v_your_name := '&sv_your_name';

new 8: v_your_name := 'THIS NAME MUST BE REALLY LONG';

My name is ELENA

Error in the outer block

This name is too long

PL/SQL procedure successfully completed.

Notice that the only difference between the two versions of the output is the line number of the bind variable. In the first version of the output, the assignment statement takes place in the declaration section of the inner block. In the second version of the output, the assignment statement occurs in the executable section of the inner block. However, all messages displayed on the screen are identical in both versions of the output.

10.3.2 Answers

In this version of the example, the exception-handling section of the inner block was modified. The DBMS_OUTPUT.PUT_LINE statement has been replaced by the RAISE statement. In addition, the exception-handling section was included in the outer block.

Notice that the error message has been modified as well. There is no course number displayed by the error message. This change is necessary because the exception-handling section of the outer block is located outside of the cursor FOR loop. Therefore, the course number is not visible by the exception. When run, this version produces the following output:

Course, 10 has 1 sections

Course, 20 has 4 sections

Course, 25 has 9 sections

There are no sections for the course

PL/SQL procedure successfully completed.

In order to produce the error message that contains the course number, the script should be modified as follows:

-- ch10_4c.sql, version 3.0
SET SERVEROUTPUT ON
DECLARE
CURSOR course_cur IS
SELECT course_no
FROM course;
v_total NUMBER;

v_course_no NUMBER;
e_no_sections EXCEPTION;
BEGIN
FOR course_rec in course_cur LOOP

v_course_no := course_rec.course_no;
BEGIN
SELECT COUNT(*)
INTO v_total
FROM section
WHERE course_no = course_rec.course_no;
IF v_total = 0 THEN
RAISE e_no_sections;
ELSE
DBMS_OUTPUT.PUT_LINE ('Course, '||
course_rec.course_no||' has '||v_total||
' sections');
END IF;
EXCEPTION
WHEN e_no_sections THEN

RAISE;
END;
END LOOP;

EXCEPTION

WHEN e_no_sections THEN

DBMS_OUTPUT.PUT_LINE ('There are no sections for '||

'the course '||v_course_no);
END;

In this version of the example, there is a new variable, v_course_no, that holds the current course number. Notice that the assignment statement for this variable is the first executable statement of the cursor FOR loop. This arrangement guarantees that the variable will have a value assigned to it before the e_no_sections exception is raised. When run, the example produces the following output:

Course, 10 has 1 sections

Course, 20 has 4 sections

Course, 25 has 9 sections

There are no sections for the course 80

PL/SQL procedure successfully completed.