Create the following script. Create an index-by table and populate it with instructor's full name. In other words, each row of the index-by table should contain first name and last name. Display this information on the screen.
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;
In the example above, the index-by table name_tab is populated with instructor full names. Notice, the variable, v_counter is used is a subscript to reference individual table elements. This example produces the output shown below:
name(1): Fernand Hanks name(2): Tom Wojick name(3): Nina Schorin name(4): Gary Pertez name(5): Anita Morris name(6): Todd Smythe name(7): Marilyn Frantzen name(8): Charles Lowry name(9): Rick Chow PL/SQL procedure successfully completed.
Modify the script created above. Instead of using an index-by table use a varray.
Answer: Your script should look similar to the script shown below. 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;
In this version of the script, you define a varray of 15 elements. It is important to remember to initialize the array before referencing its individual elements. In addition, the array must be extended before new elements are added to it.
Modify the script created above. Create an additional varray and populate it with unique course numbers that each instructor teaches. Display instructor's name and the list of courses he or she teaches.
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;
Consider the script created above. First, you declare two cursors, INSTRUCTOR_CUR and COURSE_CUR. The COURSE_CUR accepts a parameter because it returns a list of course taught by a particular instructor. Notice, the SELECT statement uses function UNIQUE to retrieve distinct course numbers. Second, you declare two varray types and variables, name_varray and course_varray. Notice that you do not initialize second varray at the time of declaration. Next, you declare two counters and initialize the first counter only.
In the body of the block, you open INSTRUCTOR_CUR, and populate name_varray with its first element. Next, you initialize the second counter and course_varray. This step is necessary because you need to repopulate course-_varray for the next instructor. Next, you open COURSE_CUR based to retrieve corresponding courses and display them on the screen. When run, the script produces the following output:
name(1): Fernand Hanks course(1): 25 course(2): 120 course(3): 122 course(4): 125 course(5): 134 course(6): 140 course(7): 146 course(8): 240 course(9): 450 =========================== name(2): Tom Wojick course(1): 25 course(2): 100 course(3): 120 course(4): 124 course(5): 125 course(6): 134 course(7): 140 course(8): 146 course(9): 240 =========================== name(3): Nina Schorin course(1): 20 course(2): 25 course(3): 100 course(4): 120 course(5): 124 course(6): 130 course(7): 134 course(8): 142 course(9): 147 course(10): 310 =========================== name(4): Gary Pertez course(1): 20 course(2): 25 course(3): 100 course(4): 120 course(5): 124 course(6): 130 course(7): 135 course(8): 142 course(9): 204 course(10): 330 =========================== name(5): Anita Morris course(1): 20 course(2): 25 course(3): 100 course(4): 122 course(5): 124 course(6): 130 course(7): 135 course(8): 142 course(9): 210 course(10): 350 =========================== name(6): Todd Smythe course(1): 20 course(2): 25 course(3): 100 course(4): 122 course(5): 125 course(6): 130 course(7): 135 course(8): 144 course(9): 220 course(10): 350 =========================== name(7): Marilyn Frantzen course(1): 25 course(2): 120 course(3): 122 course(4): 125 course(5): 132 course(6): 135 course(7): 145 course(8): 230 course(9): 350 =========================== name(8): Charles Lowry course(1): 25 course(2): 120 course(3): 122 course(4): 125 course(5): 132 course(6): 140 course(7): 145 course(8): 230 course(9): 420 =========================== name(9): Rick Chow course(1): 10 =========================== PL/SQL procedure successfully completed.
As mentioned earlier it is important to reinitialize the variable v_counter2 that is used to reference individual elements of course_varray. When this step is ommited, and the variable is initialized only once at the time declaration, the scripts generates following runtime error:
name(1): Fernand Hanks course(1): 25 course(2): 120 course(3): 122 course(4): 125 course(5): 134 course(6): 140 course(7): 146 course(8): 240 course(9): 450 name(2): Tom Wojick DECLARE * ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 33
Why do you this error occurs?
Find and explain errors in the script shown below:
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 error generated by the script above:
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 referrers to the initialization of table2 that has been declared as 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, 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 7 integers. After initialization, the varray contains three integers. As a result, it can be populated with no more than 4 additional integer numbers. So, the fifth iteration of the loop tries to extend the varray to 8 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 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 loop counter. For the second iteration of the loop, the EXTEND method adds fifth element to the varray while the second element is assigned a value of 2, and so forth.
Finally, consider 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.