January 21, 2021
Hot Topics:

Oracle Programming with PL/SQL Collections

  • By Michael Klaene
  • Send Email »
  • More Articles »


The Varray is short for Variable Array. A Varray stores elements of the same type in the order in which they are added. The number of elements in a Varray must be known at the time of its declaration. In other words, a Varray has a fixed lower and upper bounds, making it most similar to collection types from other programming languages. Once it is created and populated, each element can be accessed by a numeric index.

The following statements declare, and then populate, a Varray that will contain 4 elements of the same type as the column genre_name in table book_genre:

    TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE;
    Fiction_genres genres;
    fiction_genres := genres('MYSTERY','SUSPENSE', 'ROMANCE','HORROR');

We could have declared genres to be of type VARCHAR2(30) because all values here are text. However, in keeping with good Oracle programming practices, you should always prefer to declare variables that are based on table columns with the %TYPE attribute. This allows your code to grow with the database schema. If we were to populate genres with a variable like v_genre (versus a text literal), it would be easy for the column type to change in the database without modifying our code.

All PL/SQL collections contain a number of built-in methods that prove useful when working with them. Table 1 lists these Collection methods.

Table 1. Collection Methods
MethodAction It Performs
COUNTReturns number of elements in the Collection
EXISTSReturns Boolean true if element at specified index exists; otherwise, false
EXTENDIncreases size of Collection by 1 or number specified, ie. EXTEND(n)
**Cannot use with Associative Array
FIRSTNavigates to the first element in the Collection
LASTNavigates to the last element
PRIORNavigates to the previous element
NEXTNavigates to the next element
TRIMRemoves the last element, or the last n elements if a number is specified, ie. TRIM(n)
**Cannot use with Associative Array
DELETERemoves all elements of a Collection, or the nth element, if a parameter is specified

The following code sample demonstrates how to use a few of these methods. We are using a Varray in the example, but the methods function similarly on all collection types. We mentioned that a Varray differs from Nested Tables and Associative Arrays in that you must supply a size during its declaration. This example usese the EXTENDS method to demonstrate that it is possible to modify a Varray's size programmatically.

--Add a new genre.
IF adding_new_genre  THEN

     --Is this genre id already in the collection?
     IF NOT fiction_genres.EXISTS(v_genre_id)     THEN
       --**Add** another element to the varray.
       fiction_genres(v_genre_id) := v_genre;
   END IF;
    --Display the total # of elements.
    DBMS_OUTPUT.PUT_LINE('Total # of entries in fiction_genres is :

--Remove all entries.
IF deleting_all_genres THEN 

The advantage that Varrays (and Nested Tables) have over Associative Arrays is their ability to be added to the database. For example, you could add the genres type, a Varray, to a DML statement on the library table.

CREATE TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE;
CREATE TABLE book_library (
    library_id    NUMBER,
    name          VARCHAR2(30),
    book_genres   genres);

When a new library record is added, we can supply values to our genres type, book_genres, by using its constructor:

--Insert a new collection into the column on our book_library table.
INSERT INTO book_library (library_id, name, book_genres)
  VALUES (book_library_seq.NEXTVAL,'Brand New Library',
          Genres('FICTION','NON-FICTION', 'HISTORY',
                 'BUSINESS AND FINANCE'));

The query SELECT name, book_genres from book_library returns us:

NAME                  BOOK_GENRES
--------------------  ---------------------------------------------
                      'BUSINESS AND FINANCE')

Note how the insertion order of elements in book_genres is retained. When a table contains a Varray type, its data is included in-line, with the rest of the table's data. When a Varray datatype is selected from a database table, all elements are retrieved. The Varray is ideal for storing fixed values that will be processed collectively. It is not possible to perform inserts, updates, and deletes on the individual elements in a Varray. If you require your collection to be stored in the database but would like the flexibility to manipulate elements individually, Nested Tables are a better solution.

Page 2 of 4

This article was originally published on July 12, 2004

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date