Oracle PL/SQL by Example, Fifth Edition (2015)

Chapter 15. Collections


In this chapter, you will learn about

Image PL/SQL Tables

Image Varrays

Image Multilevel Collections


Throughout this book you have explored different types of PL/SQL identifiers or variables that represent individual elements (for example, a variable that represents a grade for a particular student). However, often in your programs you want to have the ability to represent a group of elements (for example, the grades for a class of students). To support this technique, PL/SQL provides collection data types that work just like arrays available in other third-generation programming languages.

A collection is a group of elements of the same data type. Each element is identified by a unique subscript that represents its position in the collection. In this chapter you will learn about two collection data types: tables and varrays. You will also learn about multilevel collections.

Lab 15.1: PL/SQL Tables


After this lab, you will be able to

Image Use Associative Arrays

Image Use Nested Tables

Image Use Collection Methods


A PL/SQL table is similar to a 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 Figure 15.1.

Image

Figure 15.1 PL/SQL Table

Figure 15.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, the number 3 has the 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: associative arrays (formerly known as index-by tables) and nested tables. They have the same structure, and their rows are accessed in the same way—that is, via subscript notation. The main difference between these two types is that nested tables can be stored in a database column, whereas associative arrays cannot.

Associative Arrays

The general syntax for creating an associative array is shown in Listing 15.1 (the reserved words and phrases surrounded by brackets are optional).

Listing 15.1 Associative Array

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

Notice that the declaration of an associative array requires two steps. 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 a data type of the individual elements in the arrays. The INDEX BY clause specifies which data type is used for indexing the associative array; it can be either a string type (for example, VARCHAR2) or PLS_INTEGER.


Did You Know?

An index of an associative array may be populated with any data type as long as the TO_CHAR function can convert it to VARCHAR2.


Second, the actual array variable is declared based on the type specified in the previous step. Consider the code fragment shown in Listing 15.2.

Listing 15.2 Declaration of an Associative Array

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

In this code fragment, type last_name_type is declared based on the column LAST_NAME of the STUDENT table that is indexed by PLS_INTEGER. Next, the actual associative array called last_name_tab is declared to be of a last_name_type.

As mentioned earlier, the individual elements of an associative array are referenced via subscript notation as follows:

array_name(subscript)

This technique is demonstrated in the following example.

For Example  ch15_1a.sql

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 PLS_INTEGER;
  last_name_tab last_name_type;

  v_index PLS_INTEGER := 0;
BEGIN
  FOR name_rec IN name_cur
  LOOP
    v_index := v_index + 1;
    last_name_tab(v_index) := name_rec.last_name;
    DBMS_OUTPUT.PUT_LINE ('last_name('||v_index||'): '||last_name_tab(v_index));
  END LOOP;
END;

In this example, the associative array last_name_tab is populated with last names from the STUDENT table. The variable v_index is used as a subscript to reference individual table elements. This example produces the following output:

last_name(1): Kocka
last_name(2): Jung
last_name(3): Mulroy
last_name(4): Brendler
last_name(5): Carcia
last_name(6): Tripp
last_name(7): Frost
last_name(8): Snow
last_name(9): Scrittorale


Watch Out!

Referencing a nonexistent row of the associative array 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 PLS_INTEGER;
  last_name_tab last_name_type;

  v_index PLS_INTEGER := 0;
BEGIN
  FOR name_rec IN name_cur
  LOOP
    v_index := v_index + 1;
    last_name_tab(v_index) := name_rec.last_name;
    DBMS_OUTPUT.PUT_LINE ('last_name('|| v_index ||'): '||last_name_tab(v_index));
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('last_name(10): '||last_name_tab(10));
END;

This version of the script produces the following output:

last_name(1): Kocka
last_name(2): Jung
last_name(3): Mulroy
last_name(4): Brendler
last_name(5): Carcia
last_name(6): Tripp
last_name(7): Frost
last_name(8): Snow
last_name(9): Scrittorale
ORA-01403: no data found
ORA-06512: at line 19

Notice that the DBMS_OUTPUT.PUT_LINE statement shown in bold raises the NO_DATA_FOUND exception because it references the tenth row of the associative array, even though the array contains only nine rows.


Nested Tables

The general syntax for creating a nested table is shown in Listing 15.3 (the reserved words and phrases surrounded by brackets are optional).

Listing 15.3 Nested Table

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 associative array except that there is no INDEX BY clause.

Consider the code fragment shown in Listing 15.4.

Listing 15.4 Declaration of a Nested Table

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

In this code fragment, type last_name_type is declared based on the column LAST_NAME of the STUDENT table. Next, the actual nested table called last_name_tab is declared to be of a last_name_type.

Unlike an associative array, a nested table may also be defined as a stand-alone user-defined type via the CREATE TYPE statement. This scenario is illustrated in Listing 15.5.

Listing 15.5 Define a Nested Table Type on the Schema Level

CREATE OR REPLACE TYPE last_name_type  AS TABLE OF VARCHAR2(30);
/
CREATE OR REPLACE TYPE last_name_table AS TABLE OF last_name_type;
/

In Listing 15.5, you define two stand-alone types to be created in the STUDENT schema. The first type, last_name_type, is a nested table type; the individual elements of this type can contain stings up to 30 characters long. The second type, last_name_table, is the nested table itself, which is based on the last_name_type.

A nested table must be initialized before its individual elements can be referenced. Consider the modified version of the example given earlier in this lab. Notice that the last_name_type is defined as a nested table (there is no INDEX BY clause). Affected statements are shown in bold.

For Example  ch15_1b.sql

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_index PLS_INTEGER := 0;
BEGIN
  FOR name_rec IN name_cur
  LOOP
    v_index := v_index + 1;
    last_name_tab(v_index) := name_rec.last_name;
    DBMS_OUTPUT.PUT_LINE ('last_name('|| v_index ||'): '||last_name_tab(v_index));
  END LOOP;
END;

This example causes the following error:

ORA-06531: Reference to uninitialized collection
ORA-06512: at line 15

It causes this 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. To reference the individual elements of the nested table, the table must be initialized with the help of a system-defined function called a constructor. The constructor has the same name as the nested table type.

For example, the statement

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

initializes the last_name_tab table to two elements. Most of the time, you will not know in advance which values should constitute a particular nested table. In this scenario, 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.

Now consider a modified version of the example shown previously. Changes are highlighted in bold.

For Example  ch15_1c.sql

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_index PLS_INTEGER := 0;
BEGIN
  FOR name_rec IN name_cur
  LOOP
    v_index := v_index + 1;
    last_name_tab.EXTEND;
    last_name_tab(v_index) := name_rec.last_name;

    DBMS_OUTPUT.PUT_LINE ('last_name('||v_index||'): '||last_name_tab(v_index));
  END LOOP;
END;

In this version, the nested table is initialized at the time of the declaration. As a consequence, it is empty, but non-NULL. The cursor loop includes a statement with one of the collection methods, EXTEND. This method allows you to increase the size of the collection. Note that the EXTENDmethod cannot be used with associative arrays. The next section in this lab provides a detailed explanation of the various collection methods.

Next, the nested table is assigned values just like the associative array in the original version of the example. When run, this version of the example completes successfully and produces the following output:

last_name(1): Kocka
last_name(2): Jung
last_name(3): Mulroy
last_name(4): Brendler
last_name(5): Carcia
last_name(6): Tripp
last_name(7): Frost
last_name(8): Snow
last_name(9): Scrittorale


Did You Know?

What is the difference between NULL and empty collections? 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_index PLS_INTEGER := 1;
BEGIN
  DBMS_OUTPUT.PUT_LINE (integer_tab(v_index));
END;

ORA-06531: Reference to uninitialized collection
ORA-06512: at line 7

If a collection has been initialized so that it is non-NULL yet 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_index PLS_INTEGER := 1;
BEGIN
  DBMS_OUTPUT.PUT_LINE (integer_tab(v_index));
END;

ORA-06533: Subscript beyond count
ORA-06512: at line 7


Collection Methods

In example ch15_1c.sql, you saw one of the collection methods—in this case, EXTEND. A collection method is either a built-in procedure or a function that is called using dot notation as shown in Listing 15.6.

Listing 15.6 Invoking a Collection Method

collection_name.method_name

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

Image EXISTS: This function returns TRUE if a specified element exists in a collection and can be used to avoid raising SUBSCRIPT_OUTSIDE_LIMIT exceptions.

Image COUNT: This function returns the total number of elements in a collection.

Image EXTEND: This procedure increases the size of a collection.

Image DELETE: This procedure deletes either all elements, just the elements in the specified range, or a particular element from a collection. PL/SQL keeps placeholders of the deleted elements.

Image FIRST and LAST: These functions return subscripts of the first and last elements of a collection. If the first element of a nested table is deleted, the FIRST method returns a value greater than 1. If elements are deleted from the middle of a nested table, the LAST method returns a value greater than the COUNT method.

Image PRIOR and NEXT: These functions return subscripts that precede and succeed a specified collection subscript.

Image TRIM: This procedure removes either one or a specified number of elements from the end of a collection. PL/SQL does not keep placeholders for the trimmed elements.


Watch Out!

The EXTEND and TRIM methods cannot be used with associative arrays.


The following example illustrates the use of various collection methods.

For Example  ch15_2a.sql

DECLARE
  TYPE index_by_type IS TABLE OF NUMBER
    INDEX BY PLS_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 associative array
  FOR i IN 1..10
  LOOP
    index_by_table(i) := i;
  END LOOP;

  -- Check if the associative array has third element
  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 associative array
  index_by_table.DELETE(10);
  -- Delete 10th element from nested table
  nested_table.DELETE(10);
  -- Delete elements 1 through 3 from nested table
  nested_table.DELETE(1,3);

  -- Get element counts for associative array and nested table
  DBMS_OUTPUT.PUT_LINE ('index_by_table.COUNT = '||index_by_table.COUNT);
  DBMS_OUTPUT.PUT_LINE ('nested_table.COUNT   = '||nested_table.COUNT);
  -- Get first and last indexes of the associative array
  -- and nested table
  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.FIRST   = '||nested_table.FIRST);
  DBMS_OUTPUT.PUT_LINE ('nested_table.LAST    = '||nested_table.LAST);

  -- Get indexes that precede and succeed 2nd indexes of the associative array
  -- and nested table
  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));
  DBMS_OUTPUT.PUT_LINE ('nested_table.PRIOR(2)   = '||nested_table.PRIOR(2));
  DBMS_OUTPUT.PUT_LINE ('nested_table.NEXT(2)    = '||nested_table.NEXT(2));

  -- Delete last two elements of the nested table
  nested_table.TRIM(2);
  -- Delete last element of the nested table
  nested_table.TRIM;

  -- Get last index of the nested table
  DBMS_OUTPUT.PUT_LINE('nested_table.LAST = '||nested_table.LAST);
END;

Examine the output returned by the preceding example:

index_by_table(3)       = 3
index_by_table.COUNT    = 9
nested_table.COUNT      = 6
index_by_table.FIRST    = 1
index_by_table.LAST     = 9
nested_table.FIRST      = 4
nested_table.LAST       = 9
index_by_table.PRIOR(2) = 1
index_by_table.NEXT(2)  = 3
nested_table.PRIOR(2)   =
nested_table.NEXT(2)    = 4
nested_table.LAST       = 7

The first line of the output

index_by_table(3) = 3

demonstrates that the EXISTS method returns TRUE. As a result, the IF statement

IF index_by_table.EXISTS(3)
THEN
  ...

evaluates to TRUE as well.

The second and third lines of the output

index_by_table.COUNT = 9
nested_table.COUNT   = 6

show the results of the COUNT method after some elements were deleted from the associative array and nested table.

Next, the fourth through seventh lines of the output

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

show the results of the 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 8 through 11 of the output

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

show the results of the 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 of the nested table were removed. Once the DELETE method is issued, the PL/SQL keeps placeholders of the deleted elements. Therefore, the first call of the TRIM method removed the ninth and tenth elements from the nested table, and the second call of the TRIM method removed the eighth element from the nested table. As a result, the LAST method returned the value 7 as the last subscript of the nested table.

Lab 15.2: Varrays


After this lab, you will be able to

Image Use Arrays


A varray—that is, a variable-size array—is another collection type. Similar to PL/SQL tables, each element of a varray is assigned a consecutive subscript starting at 1. Figure 15.2 shows a varray consisting of five integer numbers, where each number is assigned a unique subscript that corresponds to its position in the varray.

Image

Figure 15.2 Varray

A varray has a maximum size. In other words, a subscript of a varray has a fixed lower bound equal to 1, and an upper bound that is extensible if such a need arises. In Figure 15.2, the upper bound of a varray is 5, but it can be extended to 6, 7, and so on, up to 10. Therefore, a varray can contain a number of elements, varying from zero (empty array) to its maximum size. Recall that PL/SQL tables do not have a maximum size that must be specified explicitly.

The general syntax for creating a varray is shown in Listing 15.7 (the reserved words and phrases surrounded by brackets are optional).

Listing 15.7 Varray

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
varray_name TYPE_NAME;

First, a varray 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 varray. Notice that there are two variations of the type, VARRAY and VARYING ARRAY. A size_limit is a positive integer literal that specifies the upper bound of a varray.

Second, the actual varray is declared based on the type specified in the first step.

Consider the code fragment shown in Listing 15.8.

Listing 15.8 Declaring a Varray

DECLARE
  TYPE last_name_type IS VARRAY(10) OF student.last_name%TYPE;
  last_name_varray last_name_type;

In this example, type last_name_type is declared as a varray of 10 elements based on the column LAST_NAME of the STUDENT table. Next, the actual varray last_name_varray is declared based on the last_name_type. Similarly to nested tables, a varray may be defined as a stand-alone user-defined type via the CREATE TYPE statement.

Just like a nested table, a varray is automatically NULL when it is declared and must be initialized before its individual elements can be referenced. In the following modified version of an example used in Lab 15.1, ch15_1c.sql, instead of using a nested table, the script version uses varray (modified statements are highlighted in bold).

For Example  ch15_1d.sql

DECLARE
  CURSOR name_cur IS
    SELECT last_name
      FROM student
     WHERE rownum < 10;

  TYPE last_name_type IS VARRAY(10) OF student.last_name%TYPE;
  last_name_varray last_name_type := last_name_type();

  v_index PLS_INTEGER := 0;
BEGIN
  FOR name_rec IN name_cur
  LOOP
    v_index := v_index + 1;
    last_name_varray.EXTEND;
    last_name_varray(v_index) := name_rec.last_name;

    DBMS_OUTPUT.PUT_LINE ('last_name('||v_index||'): '||last_name_varray(v_index));
  END LOOP;
END;

This example produces the following output:

last_name(1): Kocka
last_name(2): Jung
last_name(3): Mulroy
last_name(4): Brendler
last_name(5): Carcia
last_name(6): Tripp
last_name(7): Frost
last_name(8): Snow
last_name(9): Scrittorale

Based on the preceding example, you might realize that collection methods seen in Lab 15.1 can be used with varrays as well. Consider the following example, which illustrates the use of various collection methods when applied to a varray:

For Example  ch15_3a.sql

DECLARE
  TYPE varray_type IS VARRAY(10) OF NUMBER;
  varray varray_type := varray_type(1, 2, 3, 4, 5, 6);

BEGIN
  DBMS_OUTPUT.PUT_LINE ('varray.COUNT = '||varray.COUNT);
  DBMS_OUTPUT.PUT_LINE ('varray.LIMIT = '||varray.LIMIT);

  DBMS_OUTPUT.PUT_LINE ('varray.FIRST = '||varray.FIRST);
  DBMS_OUTPUT.PUT_LINE ('varray.LAST  = '||varray.LAST);
  -- Append two copies of the 4th element to the varray
  varray.EXTEND(2, 4);
  DBMS_OUTPUT.PUT_LINE ('varray.LAST  = '||varray.LAST);
  DBMS_OUTPUT.PUT_LINE ('varray('||varray.LAST||')  = '||varray(varray.LAST));

  -- Trim last two elements
  varray.TRIM(2);
  DBMS_OUTPUT.PUT_LINE('varray.LAST =  '||varray.LAST);
END;

This example returns the following output:

varray.COUNT = 6
varray.LIMIT = 10
varray.FIRST = 1
varray.LAST  = 6
varray.LAST  = 8
varray(8)    = 4
varray.LAST  = 6

The first two lines of the output

varray.COUNT = 6
varray.LIMIT = 10

show the results of the COUNT and LIMIT methods, respectively. Recall that the COUNT method returns the number of elements that a collection contains. This collection has been initialized to six elements, so the COUNT method returns a value of 6.

The next line of output corresponds to another collection method, LIMIT. This method returns the maximum number of elements that a collection can contain and is typically used with varrays because varrays have an upper bound specified at the time of declaration. This varray has an upper bound of 10, so the LIMIT method returns a value of 10.


Did You Know?

When the LIMIT method is used with associative arrays and nested tables, it returns NULL because those collection types do not have a maximum size.


The third and fourth lines of the output

varray.FIRST = 1
varray.LAST  = 6

show the results of the FIRST and LAST methods. The fifth and six lines of the output

varray.LAST = 8
varray(8)   = 4

show the results of the LAST method and the value of the eighth element of the collection after the EXTEND method has increased the size of the collection. Notice that the EXTEND method

varray.EXTEND(2, 4);

appends two copies of the fourth element to the collection. As a result, the seventh and eighth elements both contain a value of 4.

Finally, the last line of output

varray.LAST = 6

shows the value of the last subscript after the last two elements were removed via the TRIM method.


Watch Out!

You cannot use the DELETE method with a varray to remove its elements. Unlike PL/SQL tables, varrays are dense, and using the DELETE method causes an error, as illustrated in the following example:

DECLARE
  TYPE varray_type IS VARRAY(3) OF CHAR(1);
  varray varray_type := varray_type('A', 'B', 'C');
BEGIN
  varray.DELETE(3);
END;

ORA-06550: line 6, column 4:
PLS-00306: wrong number or types of arguments in call to 'DELETE'
ORA-06550: line 6, column 4:
PL/SQL: Statement ignored


You have now seen how to define and use all three collection data types: associative arrays, nested tables, and varrays. Table 15.1 summarizes their similarities and differences.

Image

Table 15.1 Associative Arrays, Nested Tables, and Varrays

Lab 15.3: Multilevel Collections


After this lab, you will be able to

Image Use Multilevel Collections


So far, you have seen various examples of collections with the element type based on a scalar type, such as NUMBER and VARCHAR2. However, PL/SQL provides you with the ability to create collections whose element type is based on a collection type. Such collections are called multilevel collections.

Figure 15.3 shows a varray of varrays, also called a nested varray. In this case, the varray of varrays consists of three elements, where each individual element is a varray consisting of four integer numbers.

Image

Figure 15.3 A Varray of Varrays

To reference an individual element of a varray of varrays, you use the notation as shown in Listing 15.9.

Listing 15.9 Referencing an Element of a Nested Varray

varray_name(subscript of the outer varray)(subscript of the inner varray)

For example, varray(1)(3) in Figure 15.3 equals 6; similarly, varray(2)(1) equals 1. Now consider the following example based on Figure 15.3.

For Example  ch15_4a.sql

DECLARE
  TYPE varray_type1 IS VARRAY(4) OF INTEGER;
  TYPE varray_type2 IS VARRAY(3) OF varray_type1;

  varray1 varray_type1 := varray_type1(2, 4, 6, 8);
  varray2 varray_type2 := varray_type2(varray1);
BEGIN
  DBMS_OUTPUT.PUT_LINE ('Varray of integers');
  FOR i IN 1..4
  LOOP
    DBMS_OUTPUT.PUT_LINE ('varray1('||i||'): '||varray1(i));
  END LOOP;

  varray2.EXTEND;
  varray2(2) := varray_type1(1, 3, 5, 7);

  DBMS_OUTPUT.PUT_LINE (chr(10)||'Varray of varrays of integers');
  FOR i IN 1..2
  LOOP
    FOR j IN 1..4
    LOOP
      DBMS_OUTPUT.PUT_LINE ('varray2('||i||')('||j||'): '||varray2(i)(j));
    END LOOP;
  END LOOP;
END;

In the declaration portion of the example, you define two varray types. The first type, varray_type1, is based on the INTEGER data type and can contain up to four elements. The second type, varray_type2, is based on the varray_type1 and can contain up to three elements, where each individual element may itself contain up to four elements. Next, you declare two varrays based on the types just described. The first varray, varray1, is declared as varray_type1 and initialized so that its four elements are populated with the first four even numbers. The second varray, varray2, is declared as varray_type2, so that each individual element is a varray consisting of four integer numbers, and initialized so that its first varray element is populated.

In the executable portion of the example, you display the values of the varray1 on the screen. Next, you extend the upper bound of the varray2 by 1, and populate its second element as follows:

varray2(2) := varray_type1(1, 3, 5, 7);

Here you are using a constructor corresponding to the varray_type1 because each element of the varray2 is based on the varray1 collection. In other words, the same result could be achieved via the following two statements:

varray1(2) := varray_type1(1, 3, 5, 7);
varray2(2) := varray_type2(varray1);

Once the second element of the varray2 is populated, you display the results on the screen via nested numeric FOR loops.

This example produces the following output:

Varray of integers
varray1(1): 2
varray1(2): 4
varray1(3): 6
varray1(4): 8

Varray of varrays of integers
varray2(1)(1): 2
varray2(1)(2): 4
varray2(1)(3): 6
varray2(1)(4): 8
varray2(2)(1): 1
varray2(2)(2): 3
varray2(2)(3): 5
varray2(2)(4): 7

Notice the blank line separating two portions of the example output. It is included for the readability purposes and created by adding Oracle’s built-in function CHR(10) to the DBMS_OUTPUT.PUT_LINE statement. This function adds a line feed, which in turn separates two portions of the output with the blank line.

Summary

In this chapter, you learned about associative arrays, nested tables, and varrays collection types supported in PL/SQL. You also learned how to create stand-alone user-defined collection types. In addition, you discovered how to manipulate individual collection elements with the help of built-in procedures and functions designed specifically for this purpose called methods. Finally, you learned how different collection types may be nested inside another.


By the Way

The companion website provides additional exercises and suggested answers for this chapter, with discussion related to how those answers resulted. The main purpose of these exercises is to help you test the depth of your understanding by utilizing all of the skills that you have acquired throughout this chapter.