Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 18.7 Making the Objects Option WorkChapter 19Next: 19.2 Creating the New Collections

19. Nested Tables and VARRAYs

Types of Collections
Creating the New Collections
Syntax for Declaring Collection Datatypes
Using Collections
Collection Pseudo-Functions
Collection Built-Ins
Example: PL/SQL-to-Server Integration
Collections Housekeeping
Which Collection Type Should I Use?

In PL/SQL Version 2, Oracle introduced the TABLE datatype as a way of storing singly dimensioned sparse arrays in PL/SQL. Known as the "PL/SQL table," this structure is thoroughly documented in Chapter 10, PL/SQL Tables. PL/SQL8 introduces two new collection structures that have a wide range of new uses. These structures are nested tables and variable-size arrays (VARRAYs). Like PL/SQL tables, the new structures can also be used in PL/SQL programs. But what is dramatic and new is the ability to use the new collections as the datatypes of fields in conventional tables and attributes of objects. While not an exhaustive implementation of user-defined datatypes, collections offer rich new physical (and, by extension, logical) design opportunities for Oracle practitioners.

In this chapter we'll include brief examples showing how to create and use collection types both in the database and in PL/SQL programs. We'll also show the syntax for creating collection types. We'll present the three different initialization techniques with additional examples, and we'll discuss the new built-in "methods," EXTEND, TRIM, and DELETE, for managing collection content. This chapter also contains an introduction to the new "collection pseudo-functions" that Oracle8 provides to deal with nonatomic values in table columns. Although we can't cover every aspect of SQL usage, the examples will give you a sense of how important -- and useful -- these new devices can be, despite their complexity. We also include a reference section that details all of the built-in methods for collections: for each we'll show its specification, an example, and some programming considerations. The chapter concludes with a brief discussion of which type of collection is most appropriate for some common situations.

19.1 Types of Collections

Oracle now supports three types of collections:

Using a nested table or VARRAY, you can store and retrieve nonatomic data in a single column. For example, the employee table used by the HR department could store the date of birth for each employee's dependents in a single column, as shown in Table 19.1.

Table 19.1: Storing a Nonatomic Column of Dependents in a Table of Employees



Dependents_ages (Dependent_birthdate_t)


Zaphod Beeblebrox





Molly Squiggly




Joseph Josephs


Cepheus Usrbin





Deirdre Quattlebaum


It's not terribly difficult to create such a table. First we define the collection type:

CREATE TYPE Dependent_birthdate_t AS VARRAY(10) OF DATE;

Now we can use it in the table definition:

CREATE TABLE employees (
   id NUMBER,
   name VARCHAR2(50),
   ...other columns...,
   Dependents_ages Dependent_birthdate_t

We can populate this table using the following INSERT syntax, which relies on the type's default constructor to transform a list of dates into values of the proper datatype:

INSERT INTO employees VALUES (42, 'Zaphod Beeblebrox', ...,
   Dependent_birthdate_t( '12-JAN-1765', '4-JUL-1977', '22-MAR-2021'));

One slight problem: most of us have been trained to view nonatomic data as a design flaw. So why would we actually want to do this? In some situations (for those in which you don't need to scan the contents of all the values in all the rows), theoreticians and practitioners alike consider nonatomic data to be perfectly acceptable. Even the conscience of the relational model, Chris Date, suggests that relational domains could contain complex values, including lists.[1] Some database designers have believed for years that the large percentage of nonatomic data inherent in their applications demands a nonrelational solution.

[1] See Hugh Darwen and C. J. Date, "The Third Manifesto," SIGMOD Record, Volume 24 Number 1, March 1995.

Setting aside theoretical arguments about "natural" data representations, Oracle collections provide a dramatic advantage from an application programmer's perspective: you can pass an entire collection between the database and PL/SQL using a single fetch. This feature alone could have significant positive impact on application performance.

As we've mentioned, within PL/SQL both nested tables and VARRAYs are ordered collections of homogeneous elements. Both bear some resemblance to the PL/SQL Version 2 table datatype, the elder member of the "collection" family. The new types are also singly dimensioned arrays, but they differ in areas such as sparseness (not exactly), how they're initialized (via a constructor), and whether they can be null (yes).

One chief difference between nested tables and VARRAYs surfaces when we use them as column datatypes. Although using a VARRAY as a column's datatype can achieve much the same result as a nested table, VARRAY data must be predeclared to be of a maximum size, and is actually stored "inline" with the rest of the table's data.

Nested tables, by contrast, are stored in special auxiliary tables called store tables, and there is no pre-set limit on how large they can grow. For this reason, Oracle Corporation says that VARRAY columns are intended for "small" arrays, and that nested tables are appropriate for "large" arrays.

As we've mentioned, the old Version 2 table datatype is now called an index-by table , in honor of the INDEX BY BINARY_INTEGER syntax required when declaring such a type. Despite the many benefits of the new collection types, index-by tables have one important unique feature: initial sparseness. Table 19.2 illustrates many of the additional differences among index-by tables and the new collection types.

Table 19.2: Comparing Oracle Collection Types


Index-By Table

Nested Table






Usable in SQL?




Usable as column datatype in a table?


Yes; data stored "out of line" (in separate table)

Yes; data stored "in line" (in same table)

Uninitialized state

Empty (cannot be null); elements undefined

Atomically null; illegal to reference elements

Atomically null; illegal to reference elements


Automatic, when declared

Via constructor, fetch, assignment

Via constructor, fetch, assignment

In PL/SQL, elements referenced via


(-2,147,483,647 .. 2,147,483,647)

Positive integer between 1 and 2,147,483,647

Positive integer between 1 and 2,147,483,647



Initially, no; after deletions, yes




Can be extended


Can assign value to any element at any time?


No; may need to EXTEND first

No; may need to EXTEND first, and cannot EXTEND past upper bound

Means of extending

Assign value to element with a new subscript

Use built-in EXTEND procedure (or TRIM to condense), with no predefined maximum

EXTEND (or TRIM), but only up to declared maximum size

Can be compared for equality?




Retains ordering and subscripts when stored in and retrieved from database?




The inevitable question is: Which construct should I use? This chapter reviews some examples of the new collections and offers some suggestions in this area. The short answer:

We'll revisit these suggestions in more detail at the end of the chapter. Before diving in, though, let's review a few of the new terms:


A term which can have several different meanings:

  • A nested table, index-by table, or VARRAY datatype

  • A PL/SQL variable of type nested table, index-by table, or VARRAY

  • A table column of type nested table or VARRAY

Outer table

A term referring to the "enclosing" table in which you have used a nested table or VARRAY as a column's datatype

Inner table

The "enclosed" collection that is implemented as a column in a table; also known as a "nested table column"

Store table

The physical table which Oracle creates to hold values of the inner table

Unfortunately, the term "nested table" can be a bit misleading. A nested table, when declared and used in PL/SQL, is not nested at all! It is instead fairly similar to an array. Even when you use a nested table as a table column, in Oracle 8.0 you can only nest these structures to a single level. That is, your column cannot consist of a nested table of nested tables.

"Variable-size array" is also a deceptive name; one might assume, based on the fact that it is supposed to be "variable size," that it can be arbitrarily extended; quite the opposite is true. Although a VARRAY can have a variable number of elements, this number can never exceed the limit that you define when you create the type.

Previous: 18.7 Making the Objects Option WorkOracle PL/SQL Programming, 2nd EditionNext: 19.2 Creating the New Collections
18.7 Making the Objects Option WorkBook Index19.2 Creating the New Collections

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference