Nested Tables and Varrays

19.1 Types of Collections

Oracle now supports three types of collections:

• PL/SQL tables are singly dimensioned, unbounded, sparse collections of homogeneous elements and are available only in PL/SQL (see Chapter 10). These are now called index-by tables.
• Nested tables are also singly dimensioned, unbounded collections of homogeneous elements. They are initially dense but can become sparse through deletions. Nested tables are available in both PL/SQL and the database (for example, as a column in a table).
• VARRAYs, like the other two collection types, are also singly dimensioned collections of homogeneous elements. However, they are always bounded and never sparse. Like nested tables, they can be used in PL/SQL and in the database. Unlike nested tables, when you store and retrieve a VARRAY, its element order is preserved.

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
Id (NUMBER)	Name (VARCHAR2)	Dependents_ages (Dependent_birthdate_t)
10010	Zaphod Beeblebrox	12-JAN-1763 4-JUL-1977 22-MAR-2021
10020	Molly Squiggly	15-NOV-1968 15-NOV-1968
10030	Joseph Josephs
10040	Cepheus Usrbin	27-JUN-1995 9-AUG-1996 19-JUN-1997
10050	Deirdre Quattlebaum	21-SEP-1997

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'));

Differences

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.