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

Answer: Your script should look similar to the following:

SET SERVEROUTPUT ON
DECLARE
CURSOR name_cur IS
SELECT first_name||' '||last_name name
FROM instructor;
TYPE name_type IS TABLE OF VARCHAR2(50)
INDEX BY BINARY_INTEGER;
name_tab name_type;
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
name_tab(v_counter) := name_rec.name;
DBMS_OUTPUT.PUT_LINE ('name('||v_counter||'): '||
name_tab(v_counter));
END LOOP;
END;

Answer: Your script should look similar to the following. All changes are highlighted in bold.

SET SERVEROUTPUT ON
DECLARE
CURSOR name_cur IS
SELECT first_name||' '||last_name name
FROM instructor;
TYPE name_type IS VARRAY(15) OF VARCHAR2(50); 
name_varray name_type := name_type(); 
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
name_varray.EXTEND; 
name_varray(v_counter) := name_rec.name; 
DBMS_OUTPUT.PUT_LINE ('name('||v_counter||'): '||
name_varray(v_counter)); 
END LOOP;
END;

Answer: Your script should look similar to the following:

SET SERVEROUTPUT ON
DECLARE
CURSOR instructor_cur IS
SELECT instructor_id, first_name||' '||last_name name
FROM instructor;
CURSOR course_cur (p_instructor_id NUMBER) IS
SELECT unique course_no course
FROM section
WHERE instructor_id = p_instructor_id;
TYPE name_type IS VARRAY(15) OF VARCHAR2(50);
name_varray name_type := name_type();
TYPE course_type IS VARRAY(10) OF NUMBER;
course_varray course_type;
v_counter1 INTEGER := 0;
v_counter2 INTEGER;
BEGIN
FOR instructor_rec IN instructor_cur LOOP
v_counter1 := v_counter1 + 1;
name_varray.EXTEND;
name_varray(v_counter1) := instructor_rec.name;
DBMS_OUTPUT.PUT_LINE ('name('||v_counter1||'): '||
name_varray(v_counter1));
-- Initialize and populate course_varray
v_counter2 := 0;
course_varray := course_type();
FOR course_rec in
course_cur (instructor_rec.instructor_id)
LOOP
v_counter2 := v_counter2 + 1;
course_varray.EXTEND;
course_varray(v_counter2) := course_rec.course;
DBMS_OUTPUT.PUT_LINE ('course('||v_counter2||'): '||
course_varray(v_counter2));
END LOOP;
DBMS_OUTPUT.PUT_LINE ('===========================');
END LOOP;
END;

Find and explain errors in the following script:

DECLARE
TYPE varray_type1 IS VARRAY(7) OF INTEGER;
TYPE table_type2 IS TABLE OF varray_type1
 INDEX BY BINARY_INTEGER;
varray1 varray_type1 := varray_type1(1, 2, 3);
table2 table_type2 := table_type2(varray1, varray_type1(8, 9, 0));
BEGIN
DBMS_OUTPUT.PUT_LINE ('table2(1)(2): '||table2(1)(2));
FOR i IN 1..10 LOOP
varray1.EXTEND;
varray1(i) := i;
DBMS_OUTPUT.PUT_LINE ('varray1('||i||'): '||   varray1(i));
END LOOP;
END;

Answer: Consider the error generated by the preceding script:

table2 table_type2 := table_type2(varray1, varray_type1(8, 9, 0)); 
* 
ERROR at line 6: 
ORA-06550: line 6, column 26: 
PLS-00222: no function with name 'TABLE_TYPE2' exists in 
this scope 
ORA-06550: line 6, column 11: 
PL/SQL: Item ignored 
ORA-06550: line 9, column 44: 
PLS-00320: the declaration of the type of this expression
 is incomplete or 
malformed 
ORA-06550: line 9, column 4: 
PL/SQL: Statement ignored 

Notice that this error refers to the initialization of table2

, which has been declared as an index-by table of varrays. You will recall that index-by tables are not initialized prior to their use. As a result, the declaration of table2

must be modified. Furthermore, additional assignment statement must be added to the executable portion of the block as follows:

DECLARE
TYPE varray_type1 IS VARRAY(7) OF INTEGER;
TYPE table_type2 IS TABLE OF varray_type1 INDEX BY 
BINARY_INTEGER;
varray1 varray_type1 := varray_type1(1, 2, 3);
table2 table_type2; 
BEGIN
-- These statements populate index-by table 
table2(1) := varray1; 
table2(2) := varray_type1(8, 9, 0); 
DBMS_OUTPUT.PUT_LINE ('table2(1)(2): '||table2(1)(2));
FOR i IN 1..10 LOOP
varray1.EXTEND;
varray1(i) := i;
DBMS_OUTPUT.PUT_LINE ('varray1('||i||'): '||
varray1(i));
END LOOP;
END;

When run, this version produces a different error:

table2(1)(2): 2 
varray1(1): 1 
varray1(2): 2 
varray1(3): 3 
varray1(4): 4 
DECLARE 
* 
ERROR at line 1: 
ORA-06532: Subscript outside of limit 
ORA-06512: at line 14 

Notice that this is a runtime error that refers to varray1

. This error occurs because you are trying to extend varray beyond its limit. Varray1

can contain up to seven integers. After initialization, the varray contains three integers. As a result, it can be populated with no more than four additional integer numbers. So the fifth iteration of the loop tries to extend the varray to eight elements, which in turn causes a subscript beyond count error.

It is important to note that there is no correlation between the loop counter and the EXTEND method. Every time the EXTEND method is called, it increases the size of the varray by one element. Since the varray has been initialized to three elements, the EXTEND method adds a fourth element to the array for the first iteration of the loop. At this same time, the first element of the varray is assigned a value of 1 via the loop counter. For the second iteration of the loop, the EXTEND method adds a fifth element to the varray while the second element is assigned a value of 2, and so forth.

Finally, consider the error-free version of the script and its output:

DECLARE
TYPE varray_type1 IS VARRAY(7) OF INTEGER;
TYPE table_type2 IS TABLE OF varray_type1 INDEX
 BY BINARY_INTEGER;
varray1 varray_type1 := varray_type1(1, 2, 3);
table2 table_type2;
BEGIN
-- These statements populate index-by table
table2(1) := varray1;
table2(2) := varray_type1(8, 9, 0);
DBMS_OUTPUT.PUT_LINE ('table2(1)(2): '||table2(1)(2));
FOR i IN 4..7 LOOP 
varray1.EXTEND;
varray1(i) := i;
END LOOP;
-- Display elements of the varray 
FOR i IN 1..7 LOOP 
DBMS_OUTPUT.PUT_LINE ('varray1('||i||'): '|| 
varray1(i)); 
END LOOP; 
END;
table2(1)(2): 2 
varray1(1): 1 
varray1(2): 2 
varray1(3): 3 
varray1(4): 4 
varray1(5): 5 
varray1(6): 6 
varray1(7): 7 
PL/SQL procedure successfully completed.