In this exercise, you will learn more about varrays. You will need to debug the following script, which populates city_varray with 10 cities selected from the ZIPCODE table and displays its individual elements on the screen.
Create the following PL/SQL script:
-- ch18_3a.sql, version 1.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;
v_counter INTEGER := 0;
BEGIN
FOR city_rec IN city_cur LOOP
v_counter := v_counter + 1;
city_varray(v_counter) := city_rec.city;
DBMS_OUTPUT.PUT_LINE('city_varray('||v_counter||'): '||city_varray
(v_counter));
END LOOP;
END;
Execute the script, and then answer the following questions:
| a) |
What output was printed on the screen? Explain it. |
| b) |
Modify the script so that no errors are returned at runtime. |
| 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. |