Lab 18.3 Exercise Answers

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

18.3.1 Answers

a)	Execute the script ch18_4a.sql and explain the output produced.
A1:	Answer: Your output should look similar to the following: table_tab2(1)(1): 1 table_tab2(1)(2): 2 table_tab2(1)(3): 3 table_tab2(2)(1): 3 table_tab2(2)(2): 2 table_tab2(2)(3): 1 PL/SQL procedure successfully completed. The script ch18_4a.sql uses multilevel index-by tables or an index-by table of index-by tables. The declaration portion of the script defines a multilevel index-by table table_tab2 . Each row of this table is an index-by table consisting of three rows. The executable portion of the script populates the multilevel table via nested numeric FOR loops . In the first iteration of the outer loop, the inner loop populates the index-by table table_tab1 with values 1, 2, 3, and the first row of the multilevel table table_tab2 . In the second iteration of the outer loop, the inner loop populates the index-by table table_tab1 with values 3, 2, 1, and the second row of the multilevel table table_tab2 .
b)	Modify the script so that instead of using multilevel index-by tables it uses a nested table of index-by tables.
A2:	Answer: Your script should look similar to the following script. Changes are shown in bold letters . -- ch18_4b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE TYPE table_type1 IS TABLE OF integer INDEX BY BINARY_INTEGER; TYPE table_type2 IS TABLE OF table_type1; table_tab1 table_type1; table_tab2 table_type2 := table_type2(); BEGIN FOR i IN 1..2 LOOP table_tab2.EXTEND; FOR j IN 1..3 LOOP IF i = 1 THEN table_tab1(j) := j; ELSE table_tab1(j) := 4 - j; END IF; table_tab2(i)(j) := table_tab1(j); DBMS_OUTPUT.PUT_LINE ('table_tab2(' i')('j'): 'table_tab2(i)(j)); END LOOP; END LOOP; END;

In this version of the script, the table_type2 is declared as a nested table of index-by tables. Next, table_tab2 is initialized prior to its use, and its size is extended before a new element is assigned a value.

c)

Modify the script so that instead of using multilevel index-by tables it uses a nested table of varrays.

A3:

Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch18_4c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE TYPE table_type1 IS VARRAY(3) OF integer; TYPE table_type2 IS TABLE OF table_type1; table_tab1 table_type1 := table_type1(); table_tab2 table_type2 := table_type2(table_tab1); BEGIN FOR i IN 1..2 LOOP table_tab2.EXTEND; table_tab2(i) := table_type1(); FOR j IN 1..3 LOOP IF i = 1 THEN table_tab1.EXTEND; table_tab1(j) := j; ELSE table_tab1(j) := 4 - j; END IF; table_tab2(i).EXTEND; table_tab2(i)(j):= table_tab1(j); DBMS_OUTPUT.PUT_LINE ('table_tab2('i')('j'): 'table_tab2(i)(j)); END LOOP; END LOOP; END;

In this declaration section of the script, the table_type1 is defined as a varray with three integer elements, and the table_type2 is declared as a nested table of varrays. Next, table_tab1 and table_tab2 are initialized prior to their uses.

In the executable portion of the script, the size of the table_tab2 is incremented via the EXTEND method and its individual elements are initialized as follows :

  table_tab2(i) := table_type1();  

Notice that that each element is initialized via the constructor associated with the varray type table_type1 . Furthermore, in order to populate a nested table, a new varray element must be added to the each nested table element as shown:

  table_tab2(i).EXTEND;  

In other words, for the first iteration of the outer loop, there are three varray elements added to the first element of the nested table. Without this statement, the script causes the following error:

  DECLARE   *   ERROR at line 1:   ORA-06533: Subscript beyond count   ORA-06512: at line 20  

When run, this script produces output identical to the original example:

  table_tab2(1)(1): 1   table_tab2(1)(2): 2   table_tab2(1)(3): 3   table_tab2(2)(1): 3   table_tab2(2)(2): 2   table_tab2(2)(3): 1   PL/SQL procedure successfully completed.