Chapter 18 Collections

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Appendix D.  Answers to Test Your Thinking Sections


1)

Create the following script. Create an index-by table and populate it with the instructor's full name. In other words, each row of the index-by table should contain first name, middle initial, and last name. Display this information on the screen.

A1:

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 preceding example, the index-by table name_tab is populated with instructor full names. Notice that the variable v_counter is used as a subscript to reference individual table elements. This example produces the following output:

 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. 
2)

Modify the script created in 1). Instead of using an index-by table, use a varray.

A2:

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; 

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.

3)

Modify the script created in 2). 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.

A3:

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 just created. 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 that 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 the 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 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 omitted and the variable is initialized only once at the time declaration, the script generates the 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 think this error occurs?

4)

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; 
A4:

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. 


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net