Lab 18.2 Exercise Answers

This section gives you some suggested answers to the questions in Lab 18.2, 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.

18.2.1 Answers

a)	What output was printed on the screen? Explain it.
A1:	Answer: Your output should look similar to the following: DECLARE * ERROR at line 1: ORA-06531: Reference to uninitialized collection ORA-06512: at line 14 You will recall that when a varray is declared, it is automatically NULL. In other words, the collection itself is NULL, not its individual elements. Therefore, before it can be used, it must be initialized via the constructor function with the same name as the varray type. Furthermore, once the collection is initialized , the EXTEND method must be used before its individual elements can be referenced in the script.
b)	Modify the script so that no errors are returned at runtime.
A2:	Answer: Your script should look similar to the following script. Changes are shown in bold letters . -- ch18_3b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE CURSOR city_cur IS SELECT city FROM zipcode WHERE rownum <= 10; TYPE city_type IS VARRAY(10) OF zipcode .city%TYPE; city_varray city_type := city_type(); v_counter INTEGER := 0; BEGIN FOR city_rec IN city_cur LOOP v_counter := v_counter + 1; city_varray.EXTEND; city_varray(v_counter) := city_rec.city; DBMS_OUTPUT.PUT_LINE('city_varray('v_counter '): 'city_varray(v_counter)); END LOOP; END;

When run, this script produces the following output:

  city_varray(1): Santurce   city_varray(2): North Adams   city_varray(3): Dorchester   city_varray(4): Tufts Univ. Bedford   city_varray(5): Weymouth   city_varray(6): Sandwich   city_varray(7): Ansonia   city_varray(8): Middlefield   city_varray(9): Oxford   city_varray(10): New Haven   PL/SQL procedure successfully completed.  

c)

Modify the script as follows : Double the size of the varray and populate the last ten elements with the first ten elements. In other words, the value of the eleventh element should be equal to the value of the first element; the value of the twelfth element should be equal to the value of the second element; and so forth.

A3:

Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch18_3c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE CURSOR city_cur IS SELECT city FROM zipcode WHERE rownum <= 10; TYPE city_type IS VARRAY(20) OF zipcode.city%TYPE; city_varray city_type := city_type(); v_counter INTEGER := 0; BEGIN FOR city_rec IN city_cur LOOP v_counter := v_counter + 1; city_varray.EXTEND; city_varray(v_counter) := city_rec.city; END LOOP; FOR i IN 1..v_counter LOOP -- extend the size of varray by 1 and copy the -- current element to the last element city_varray.EXTEND(1, i); END LOOP; FOR i IN 1..20 LOOP DBMS_OUTPUT.PUT_LINE('city_varray('i'): 'city_varray(i)); END LOOP; END;

In the preceding script, you increase the maximum size of the varray to 20 at the time of city_type declaration. After the first 10 elements of the varray are populated, the last ten elements are populated via numeric FOR loop and the EXTEND method as follows:

  FOR i IN 1..v_counter LOOP   -- extend the size of varray by 1 and copy the current   -- element to the last element   city_varray.EXTEND(1, i);   END LOOP;  

In this loop, the loop counter is implicitly incremented by one. So for the first iteration of the loop, the size of the varray is increased by one and the first element of the varray is copied to the eleventh element. In the same manner, the second element of the varray is copied to the twelfth element, and so forth.

In order to display all elements of the varray, the DBMS_OUTPUT.PUT_LINE statement has been moved to its own numeric FOR loop that iterates 20 times.

When run, this script produces the following output:

  city_varray(1): Santurce   city_varray(2): North Adams   city_varray(3): Dorchester   city_varray(4): Tufts Univ. Bedford   city_varray(5): Weymouth   city_varray(6): Sandwich   city_varray(7): Ansonia   city_varray(8): Middlefield   city_varray(9): Oxford   city_varray(10): New Haven   city_varray(11): Santurce   city_varray(12): North Adams   city_varray(13): Dorchester   city_varray(14): Tufts Univ. Bedford   city_varray(15): Weymouth   city_varray(16): Sandwich   city_varray(17): Ansonia   city_varray(18): Middlefield   city_varray(19): Oxford   city_varray(20): New Haven   PL/SQL procedure successfully completed.