Lab 18.1 PLSQL Tables

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 18.  Collections


Lab 18.1 PL/SQL Tables

Lab Objectives

After this Lab, you will be able to:

  • Use Index-By Tables

  • Use Nested Tables

A PL/SQL table is similar to one-column database table. The rows of a PL/SQL table are not stored in any predefined order, yet when they are retrieved in a variable each row is assigned a consecutive subscript starting at 1, as shown in the in Figure 18.1.

Figure 18.1. PL/SQL Table

graphics/18fig01.gif

Figure 18.1 shows a PL/SQL table consisting of integer numbers. Each number is assigned a unique subscript that corresponds to its position in the table. For example, number 3 has subscript 5 assigned to it because it is stored in the fifth row of the PL/SQL table.

There are two types of PL/SQL tables: index-by tables and nested tables. They have the same structure, and their rows are accessed in the same way via subscript notation as shown in Figure 18.1. The main difference between these two types is that nested tables can be stored in a database column, and the index-by tables cannot.

Index-By Tables

The general syntax for creating an index-by table is as follows (the reserved words and phrases surrounded by brackets are optional):

 TYPE type_name IS TABLE OF element_type [NOT NULL]     INDEX BY BINARY_INTEGER;  table_name TYPE_NAME; 

Notice that there are two steps in the declaration of an index-by table. First, a table structure is defined using the TYPE statement, where TYPE_NAME is the name of the type that is used in the second step to declare an actual table. An ELEMENT_TYPE is any PL/SQL datatype, such as NUMBER, VARCHAR2, or DATE, with some restrictions. The majority of restricted datatypes are beyond the scope of this book and are not mentioned in this chapter. However, you can find the complete list in Oracle help available online. Second, the actual table is declared based on the type specified in the previous step. Consider the following code fragment.

graphics/intfig03.gif FOR EXAMPLE

 DECLARE     TYPE last_name_type IS TABLE OF student.last_name%TYPE        INDEX BY BINARY_INTEGER;     last_name_tab last_name_type; 

In this example, type last_name_type is declared based on the column LAST_NAME of the STUDENT table. Next, the actual index-by table last_name_tab is declared as LAST_NAME_TYPE.

As mentioned earlier, the individual elements of a PL/SQL table are referenced via subscript notation as follows:

 table_name(subscript) 

This technique is demonstrated in the following example.

graphics/intfig03.gif FOR EXAMPLE

 DECLARE     CURSOR name_cur IS        SELECT last_name          FROM student         WHERE rownum <= 10;     TYPE last_name_type IS TABLE OF student.last_name%TYPE        INDEX BY BINARY_INTEGER;     last_name_tab last_name_type;     v_counter INTEGER := 0;  BEGIN     FOR name_rec IN name_cur LOOP        v_counter := v_counter + 1;        last_name_tab(v_counter) := name_rec.last_name;        DBMS_OUTPUT.PUT_LINE ('last_name('||v_counter||'): '||           last_name_tab(v_counter));     END LOOP;  END; 

In this example, the index-by table last_name_tab is populated with last names from the STUDENT table. Notice that the variable v_counter is used as a subscript to reference individual table elements. This example produces the following output:

 last_name(1): Crocitto  last_name(2): Landry  last_name(3): Enison  last_name(4): Moskowitz  last_name(5): Olvsade  last_name(6): Mierzwa  last_name(7): Sethi  last_name(8): Walter  last_name(9): Martin  last_name(10): Noviello  PL/SQL procedure successfully completed. 

graphics/intfig07.gif

It is important to note that referencing a nonexistent row raises the NO_DATA_FOUND exception as follows:

 DECLARE     CURSOR name_cur IS        SELECT last_name          FROM student         WHERE rownum <= 10;     TYPE last_name_type IS TABLE OF student.last_name%TYPE        INDEX BY BINARY_INTEGER;     last_name_tab last_name_type;     v_counter INTEGER := 0;  BEGIN     FOR name_rec IN name_cur LOOP        v_counter := v_counter + 1;        last_name_tab(v_counter) := name_rec.last_name;        DBMS_OUTPUT.PUT_LINE ('last_name('||v_counter||           '): '||last_name_tab(v_counter));     END LOOP;     DBMS_OUTPUT.PUT_LINE ('last_name(11): '||        last_name_tab(11));  END; 

This example produces the output shown below:

 last_name(1): Crocitto  last_name(2): Landry  last_name(3): Enison  last_name(4): Moskowitz  last_name(5): Olvsade  last_name(6): Mierzwa  last_name(7): Sethi  last_name(8): Walter  last_name(9): Martin  last_name(10): Noviello  DECLARE  *  ERROR at line 1:  ORA-01403: no data found  ORA-06512: at line 19 

Notice that the DBMS_OUTPUT.PUT_LINE statement shown in bold letters raises the NO_DATA_FOUND exception because it references the eleventh row of the table, even though the table contains only ten rows.


Nested Tables

The general syntax for creating a nested table is as follows (the reserved words and phrases surrounded by brackets are optional):

 TYPE type_name IS TABLE OF element_type [NOT NULL];  table_name TYPE_NAME; 

Notice that this declaration is very similar to the declaration of an index-by table except that there is no

 INDEX BY BINARY_INTEGER 

clause. Just like in the case of an index-by table, there are restrictions that apply to an ELEMENT_TYPE of a nested table. These restrictions are listed in Oracle help available online.

It is important to note that a nested table must be initialized before its individual elements can be referenced. Consider the modified version of the example used earlier in this lab. Notice that the last_name_type is defined as a nested table (there is no INDEX BY clause).

graphics/intfig03.gif FOR EXAMPLE

 DECLARE     CURSOR name_cur IS        SELECT last_name          FROM student         WHERE rownum <= 10;     TYPE last_name_type IS TABLE OF student.last_name%TYPE;     last_name_tab last_name_type;     v_counter INTEGER := 0;  BEGIN     FOR name_rec IN name_cur LOOP        v_counter := v_counter + 1;        last_name_tab(v_counter) := name_rec.last_name;        DBMS_OUTPUT.PUT_LINE ('last_name('||v_counter||'): '||           last_name_tab(v_counter));     END LOOP;  END; 

This example causes the following error:

 DECLARE  *  ERROR at line 1:  ORA-06531: Reference to uninitialized collection  ORA-06512: at line 14 

The example causes an error because a nested table is automatically NULL when it is declared. In other words, there are no individual elements yet because the nested table itself is NULL. In order to reference the individual elements of the nested table, it must be initialized with the help of a system-defined function called constructor. The constructor has the same name as the nested table type. For example,

 last_name_tab := last_name_type('Rosenzweig',     'Silvestrova'); 

This statement initializes the last_name_tab table to two elements. Note that most of the time, it is not known in advance what values should constitute a particular nested table. So, the following statement produces an empty but non-null nested table.

 last_name_tab := last_name_type(); 

Notice that there are no arguments passed to a constructor.

Consider a modified version of the example shown previously.

graphics/intfig03.gif FOR EXAMPLE

 DECLARE     CURSOR name_cur IS        SELECT last_name          FROM student        WHERE rownum <= 10;     TYPE last_name_type IS TABLE OF student.last_name%TYPE;     last_name_tab last_name_type := last_name_type();     v_counter INTEGER := 0;  BEGIN     FOR name_rec IN name_cur LOOP        v_counter := v_counter + 1;        last_name_tab.EXTEND;        last_name_tab(v_counter) := name_rec.last_name;        DBMS_OUTPUT.PUT_LINE ('last_name('||v_counter||'): '||           last_name_tab(v_counter));     END LOOP;  END; 

In this version, the nested table is initialized at the time of the declaration. This means that it is empty, but non-null. In the cursor loop, there is a statement with one of the collection methods, EXTEND. This method allows you to increase the size of the collection. Note that the EXTEND method cannot be used with index-by tables. You will see detailed explanation of various collection methods later in this chapter.

Next, the nested table is assigned values just like the index-by table in the original version of the example. When run, the script produces the following output:

 last_name(1): Crocitto  last_name(2): Landry  last_name(3): Enison  last_name(4): Moskowitz  last_name(5): Olvsade  last_name(6): Mierzwa  last_name(7): Sethi  last_name(8): Walter  last_name(9): Martin  last_name(10): Noviello  PL/SQL procedure successfully completed. 

graphics/intfig07.gif

It is important to note the difference between NULL collection and empty collection. If a collection has not been initialized, referencing its individual elements causes the following error:

 DECLARE     TYPE integer_type IS TABLE OF INTEGER;     integer_tab integer_type;     v_counter integer := 1;  BEGIN     DBMS_OUTPUT.PUT_LINE (integer_tab(v_counter));  END;  DECLARE  *  ERROR at line 1:  ORA-06531: Reference to uninitialized collection  ORA-06512: at line 7 

If a collection has been initialized so that it is empty, referencing its individual elements causes a different error:

 DECLARE     TYPE integer_type IS TABLE OF INTEGER;     integer_tab integer_type := integer_type();     v_counter integer := 1;  BEGIN     DBMS_OUTPUT.PUT_LINE (integer_tab(v_counter));  END;  DECLARE  *  ERROR at line 1:  ORA-06533: Subscript beyond count  ORA-06512: at line 7 


Collection Methods

In the previous examples, you have seen one of the collection methods, EXTEND. A collection method is a built-in function that is called using a dot notation as follows:

 collection_name.method_date 

The following list explains collection methods that allow you to manipulate or gain information about a particular collection:

  • EXISTS Returns TRUE if a specified element exists in a collection. This method can be used to avoid raising SUBSCRIPT_OUTSIDE_ LIMIT exceptions.

  • COUNT Returns the total number of elements in a collection.

  • EXTEND Increases the size of a collection.

  • DELETE Deletes either all elements, elements in the specified range, or a particular element from a collection. Note that PL/SQL keeps placeholders of the deleted elements.

  • FIRST and LAST Return subscripts of the first and last elements of a collection. Note that if first elements of a nested table are deleted, the FIRST method returns a value greater than one. If elements have been deleted from the middle of a nested table, the LAST method returns a value greater than the COUNT method.

  • PRIOR and NEXT Return subscripts that precede and succeed a specified collection subscript.

  • TRIM Removes either one or a specified number of elements from the end of a collection. Note that PL/SQL does not keep placeholders for the trimmed elements.

graphics/intfig07.gif

Note that DELETE and TRIM methods cannot be used with index-by tables.


Consider the following example, which illustrates the use of various collection methods.

graphics/intfig03.gif FOR EXAMPLE

 DECLARE     TYPE index_by_type IS TABLE OF NUMBER        INDEX BY BINARY_INTEGER;     index_by_table index_by_type;     TYPE nested_type IS TABLE OF NUMBER;     nested_table nested_type := nested_type(1, 2, 3, 4, 5,        6, 7, 8, 9, 10);  BEGIN     -- Populate index by table     FOR i IN 1..10 LOOP        index_by_table(i) := i;     END LOOP;     IF index_by_table.EXISTS(3) THEN        DBMS_OUTPUT.PUT_LINE ('index_by_table(3) = '||           index_by_table(3));     END IF;     -- delete 10th element from a collection     nested_table.DELETE(10);     -- delete elements 1 through 3 from a collection     nested_table.DELETE(1,3);     index_by_table.DELETE(10);     DBMS_OUTPUT.PUT_LINE ('nested_table.COUNT = '||        nested_table.COUNT);     DBMS_OUTPUT.PUT_LINE ('index_by_table.COUNT = '||        index_by_table.COUNT);     DBMS_OUTPUT.PUT_LINE ('nested_table.FIRST = '||        nested_table.FIRST);     DBMS_OUTPUT.PUT_LINE ('nested_table.LAST = '||        nested_table.LAST);     DBMS_OUTPUT.PUT_LINE ('index_by_table.FIRST = '||        index_by_table.FIRST);     DBMS_OUTPUT.PUT_LINE ('index_by_table.LAST = '||        index_by_table.LAST);     DBMS_OUTPUT.PUT_LINE ('nested_table.PRIOR(2) = '||        nested_table. PRIOR(2));     DBMS_OUTPUT.PUT_LINE ('nested_table.NEXT(2) = '||        nested_table.NEXT(2));     DBMS_OUTPUT.PUT_LINE ('index_by_table.PRIOR(2) = '||        index_by_table.PRIOR(2));     DBMS_OUTPUT.PUT_LINE ('index_by_table.NEXT(2) = '||        index_by_table.NEXT(2));     -- Trim last two elements     nested_table.TRIM(2);     -- Trim last element     nested_table.TRIM;     DBMS_OUTPUT.PUT_LINE('nested_table.LAST = '||        nested_table.LAST);  END; 

Consider the output returned by the example:

 index_by_table(3) = 3  nested_table.COUNT = 6  index_by_table.COUNT = 9  nested_table.FIRST = 4  nested_table.LAST = 9  index_by_table.FIRST = 1  index_by_table.LAST = 9  nested_table.PRIOR(2) =  nested_table.NEXT(2) = 4  index_by_table.PRIOR(2) = 1  index_by_table.NEXT(2) = 3  nested_table.LAST = 7  PL/SQL procedure successfully completed. 

The first line of the output

 index_by_table(3) = 3 

is produced because the EXISTS method returns TRUE, and as a result, the IF statement

 IF index_by_table.EXISTS(3) THEN     DBMS_OUTPUT.PUT_LINE ('index_by_table(3) = '||        index_by_table(3));  END IF; 

evaluates to TRUE as well.

The second and third lines of the output

 nested_table.COUNT = 6  index_by_table.COUNT = 9 

show the results of method COUNT after some elements were deleted from the index-by and nested tables.

Next, lines four through seven of the output

 nested_table.FIRST = 4  nested_table.LAST = 9  index_by_table.FIRST = 1  index_by_table.LAST = 9 

show the results of FIRST and LAST methods. Notice that the FIRST method applied to the nested table returns 4 because the first three elements were deleted earlier.

Next, lines eight through eleven of the output

 nested_table.PRIOR(2) =  nested_table.NEXT(2) = 4  index_by_table.PRIOR(2) = 1  index_by_table.NEXT(2) = 3 

show the results of PRIOR and NEXT methods. Notice that the PRIOR method applied to the nested table returns NULL because the first element was deleted earlier.

Finally, the last line of the output

 nested_table.LAST = 7 

shows the value of the last subscript after the last three elements were removed. As mentioned earlier, once the DELETE method is issued, the PL/SQL keeps placeholders of the deleted elements. Therefore, the first call of the TRIM method removed ninth and tenth elements from the nested table, and the second call of the TRIM method removed eighth element of the nested table. As a result, the LAST method returned value 7 as the last subscript of the nested table.

Lab 18.1 Exercises

18.1.1 Use Index-By Tables

In this exercise, you will learn more about index-by tables discussed earlier in the chapter.

Create the following PL/SQL script:

 -- ch18_1a.sql, version 1.0  SET SERVEROUTPUT ON  DECLARE    CURSOR course_cur IS       SELECT description         FROM course;     TYPE course_type IS TABLE OF course.description%TYPE        INDEX BY BINARY_INTEGER;     course_tab course_type;     v_counter INTEGER := 0;  BEGIN     FOR course_rec IN course_cur LOOP        v_counter := v_counter + 1;        course_tab(v_counter) := course_rec.description;     END LOOP;  END; 

Answer the following questions:

a)

Explain the script ch18_1a.sql.

b)

Modify the script so that rows of the index-by table are displayed on the screen.

c)

Modify the script so that only first and last rows of the index-by table are displayed on the screen.

d)

Modify the script by adding the following statements and explain the output produced:

  1. Display the total number of elements in the index-by table after it has been populated on the screen.

  2. Delete the last element, and display the total number of elements of the index-by table again.

  3. Delete the fifth element, and display the total number of elements and the subscript of the last element of the index-by table again.

18.1.2 Use Nested Tables

In this exercise, you will learn more about nested tables discussed earlier in this chapter.

Answer the following questions:

a)

Modify the script 18_1a.sql used in Exercise 18.1.1. Instead of using an index-by table, use a nested table.

b)

Modify the script by adding the following statements and explain the output produced:

  1. Delete the last element of the nested table, and then reassign a new value to it. Execute the script.

  2. Trim the last element of the nested table, and then reassign a new value to it. Execute the script.

c)

How would you modify the script created, so that there is no error generated when a new value is assigned to the trimmed element?

Lab 18.1 Exercise Answers

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

a)

Explain the script ch18_1a.sql.

A1:

Answer: The declaration section of the script contains definition of the index-by table type, course_type. This type is based on the column DESCRIPTION of the table COURSE. Next, the actual index-by table is declared as course_tab.

The executable section of the script populates the course_tab table in the cursor FOR loop. Each element of the index-by table is referenced by its subscript, v_counter. For each iteration of the loop, the value of v_counter is incremented by 1 so that each new description value is stored in the new row of the index-by table.

b)

Modify the script so that rows of the index-by table are displayed on the screen.

A2:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch18_1b.sql, version 2.0  SET SERVEROUTPUT ON  DECLARE     CURSOR course_cur IS        SELECT description          FROM course;     TYPE course_type IS TABLE OF course.description%TYPE        INDEX BY BINARY_INTEGER;     course_tab course_type;     v_counter INTEGER := 0;  BEGIN     FOR course_rec IN course_cur LOOP        v_counter := v_counter + 1;        course_tab(v_counter):= course_rec.description;        DBMS_OUTPUT.PUT_LINE('course('||v_counter||'): '||           course_tab(v_counter));     END LOOP;  END; 

Consider another version of the same script.

 -- ch18_1c.sql, version 3.0  SET SERVEROUTPUT ON  DECLARE     CURSOR course_cur IS        SELECT description          FROM course;     TYPE course_type IS TABLE OF course.description%TYPE        INDEX BY BINARY_INTEGER;     course_tab course_type;     v_counter INTEGER := 0;  BEGIN     FOR course_rec IN course_cur LOOP        v_counter := v_counter + 1;        course_tab(v_counter):= course_rec.description;     END LOOP;     FOR i IN 1..v_counter LOOP        DBMS_OUTPUT.PUT_LINE('course('||i||'): '||           course_tab(i));     END LOOP;  END; 

When run, both versions produce the same output:

 course(1): DP Overview  course(2): Intro to Computers  course(3): Intro to Programming  course(4): Structured Programming Techniques  course(5): Hands-On Windows  course(6): Intro to Java Programming  course(7): Intermediate Java Programming  course(8): Advanced Java Programming  course(9): JDeveloper  course(10): Intro to Unix  course(11): Basics of Unix Admin  course(12): Advanced Unix Admin  course(13): Unix Tips and Techniques  course(14): Structured Analysis  course(15): Project Management  course(16): Database Design  course(17): Internet Protocols  course(18): Java for C/C++ Programmers  course(19): GUI Programming  course(20): Intro to SQL  course(21): Oracle Tools  course(22): PL/SQL Programming  course(23): Intro to Internet  course(24): Intro to the Basic Language  course(25): Operating Systems  course(26): Network Administration  course(27): JDeveloper Lab  course(28): Database System Principles  course(29): JDeveloper Techniques  course(30): DB Programming in Java  PL/SQL procedure successfully completed. 
c)

Modify the script so that only first and last rows of the index-by table are displayed on the screen.

A3:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch18_1d.sql, version 4.0  SET SERVEROUTPUT ON  DECLARE     CURSOR course_cur IS        SELECT description          FROM course;     TYPE course_type IS TABLE OF course.description%TYPE        INDEX BY BINARY_INTEGER;     course_tab course_type;     v_counter INTEGER := 0;  BEGIN     FOR course_rec IN course_cur LOOP        v_counter := v_counter + 1;        course_tab(v_counter) := course_rec.description;     END LOOP;     DBMS_OUTPUT.PUT_LINE('course('||course_tab.FIRST||'): '||        course_tab(course_tab.FIRST));     DBMS_OUTPUT.PUT_LINE('course('||course_tab.LAST||'): '||        course_tab(course_tab.LAST));  END; 

Consider the statements

 course_tab(course_tab.FIRST) and course_tab(course_tab.LAST) 

used in this example. While these statements look somewhat different from the statements that you have seen so far, they produce the same effect as

 course_tab(1) and course_tab(30) 

statements because, as mentioned earlier, the FIRST and LAST methods return the subscripts of the first and last elements of a collection, respectively. In this example, the index-by table contains 30 elements, where the first element has subscript of 1, and the last element has subscript of 30.

This version of the script produces the following output:

 course(1): DP Overview  course(30): DB Programming in Java  PL/SQL procedure successfully completed. 
d)

Modify the script by adding the following statements and explain the output produced:

  1. Display the total number of elements in the index-by table after it has been populated on the screen.

  2. Delete the last element, and display the total number of elements of the index-by table again.

  3. Delete the fifth element, and display the total number of elements and the subscript of the last element of the index-by table again.

A4:

Answer: Your script should look similar to the following script. All changes are shown in bold letters.

 -- ch18_1e.sql, version 5.0  SET SERVEROUTPUT ON  DECLARE     CURSOR course_cur IS        SELECT description        FROM course;     TYPE course_type IS TABLE OF course.description%TYPE        INDEX BY BINARY_INTEGER;     course_tab course_type;     v_counter INTEGER := 0;  BEGIN     FOR course_rec IN course_cur LOOP        v_counter := v_counter + 1;        course_tab(v_counter) := course_rec.description;     END LOOP;     -- Display the total number of elements in the index-by     -- table     DBMS_OUTPUT.PUT_LINE ('1. Total number of elements: '||        course_tab.COUNT);     -- Delete the last element of the index-by table     -- Display the total number of elements in the index-by     -- table     course_tab.DELETE(course_tab.LAST);     DBMS_OUTPUT.PUT_LINE ('2. Total number of elements: '||        course_tab.COUNT);     -- Delete the fifth element of the index-by table     -- Display the total number of elements in the index-by     -- table     -- Display the subscript of the last element of the     -- index-by table        course_tab.DELETE(5);     DBMS_OUTPUT.PUT_LINE ('3. Total number of elements: '||        course_tab.COUNT);     DBMS_OUTPUT.PUT_LINE ('3. The subscript of the last '||        'element: '||course_tab.LAST);  END; 

When run, this example produces the following output:

 1. Total number of elements: 30  2. Total number of elements: 29  3. Total number of elements: 28  3. The subscript of the last element: 29  PL/SQL procedure successfully completed. 

First, the total number of the elements in the index-by table is calculated via the COUNT method and displayed on the screen. Second, the last element is deleted via DELETE and LAST methods, and the total number of the elements in the index-by table is displayed on the screen again. Third, the fifth element is deleted, and the total number of the elements in the index-by table and the subscript of the last element are displayed on the screen.

Consider the last two lines on the output. After the fifth element of the index-by table is deleted, the COUNT method returns value 28, and the LAST method returns the value 29. Usually, the values returned by the COUNT and LAST methods are equal. However, when an element is deleted from the middle of the index-by table, the value returned by the LAST method is greater than the value returned by the COUNT method because the COUNT method ignores deleted elements.

18.1.2 Answers

a)

Modify the script 18_1a.sql used in Exercise 18.1.1. Instead of using an index-by table, use a nested table.

A1:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch18_2a.sql, version 1.0  SET SERVEROUTPUT ON  DECLARE     CURSOR course_cur IS        SELECT description          FROM course;     TYPE course_type IS TABLE OF course.description%TYPE;     course_tab course_type := course_type();     v_counter INTEGER := 0;  BEGIN     FOR course_rec IN course_cur LOOP        v_counter := v_counter + 1;        course_tab.EXTEND;        course_tab(v_counter) := course_rec.description;     END LOOP;  END; 
b)

Modify the script by adding the following statements and explain the output produced:

  1. Delete the last element of the nested table, and then reassign a new value to it. Execute the script.

  2. Trim the last element of the nested table, and then reassign a new value to it. Execute the script.

A2:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch18_2b.sql, version 2.0  SET SERVEROUTPUT ON  DECLARE     CURSOR course_cur IS        SELECT description          FROM course;     TYPE course_type IS TABLE OF course.description%TYPE;     course_tab course_type := course_type();     v_counter INTEGER := 0;  BEGIN     FOR course_rec IN course_cur LOOP        v_counter := v_counter + 1;        course_tab.EXTEND;        course_tab(v_counter) := course_rec.description;     END LOOP;     course_tab.DELETE(30);     course_tab(30) := 'New Course';  END; 

When run, this version of the script produces the following error:

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

In the previous version of the script, the last element of the nested table is removed via the DELETE method. As mentioned earlier, when the DELETE method is used, the PL/SQL keeps a placeholder of the deleted element. Therefore, the statement

 course_tab(30) := 'New Course'; 

does not cause any errors.

In the current version of the script, the last element of the nested table is removed via the TRIM method. In this case, the PL/SQL does not keep placeholder of the trimmed element because the TRIM method manipulates the internal size of a collection. As a result, the reference to the trimmed elements causes 'Subscript beyond count' error.

c)

How would you modify the script created, so that there is no error generated when a new value is assigned to the trimmed element?

A3:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch18_2d.sql, version 4.0  SET SERVEROUTPUT ON  DECLARE     CURSOR course_cur IS        SELECT description          FROM course;     TYPE course_type IS TABLE OF course.description%TYPE;     course_tab course_type := course_type();     v_counter INTEGER := 0;  BEGIN     FOR course_rec IN course_cur LOOP        v_counter := v_counter + 1;        course_tab.EXTEND;        course_tab(v_counter) := course_rec.description;     END LOOP;     course_tab.TRIM;     course_tab.EXTEND;     course_tab(30) := 'New Course';  END; 

In order to reference the trimmed element, the EXTEND method is use to increase the size on the collection. As a result, the assignment statement

 course_tab(30) := 'New Course'; 

does not cause any errors.

Lab 18.1 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

Answers appear in Appendix A, Section 18.1.

1)

The main difference between the index-by and nested tables is that

  1. _____ nested tables can be stored in a database column, and index-by tables cannot.

  2. _____ index-by tables can be stored in a database column, and nested tables cannot.

2)

An index-by table is indexed by what datatype?

  1. _____ NUMBER

  2. _____ INTEGER

  3. _____ BINARY_INTEGER

  4. _____ PLS_INTEGER

3)

A nested table must be initialized prior to its use.

  1. _____ True

  2. _____ False

4)

If a PL/SQL table contains one element

  1. _____ the FIRST method returns value of one, and the LAST method returns NULL.

  2. _____ the FIRST method returns NULL, and the LAST method returns value of one.

  3. _____ the FIRST and LAST methods return a value of one.

  4. _____ referring to these methods causes an error.

5)

If a PL/SQL table has eight elements, the DELETE (3, 7) method deletes

  1. _____ the third and seventh elements of the collection.

  2. _____ the third element and ignores the seventh element of the collection.

  3. _____ elements three to seven.


    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