Introduction

Oracle provides two collection types: nested tables and varying arrays or VARRAYS. A collection is an ordered group of elements of the same type. Each element from the group can be accessed using a unique subscript. The element types of a collection can be either built-in datatypes, user-defined types or references (REFs) to object types.

Nested Tables

An ordered group of items of type TABLE are called nested tables. Nested tables can contain multiple columns and can be used as variables, parameters, results, attributes, and columns. They can be thought of as one column database tables. Rows of a nested table are not stored in any particular order.

The size of a nested table can increase dynamically, i.e., nested tables are unbounded. Elements in a nested table initially have consecutive subscripts, but as elements are deleted, they can have non-consecutive subscripts.

Nested tables can be fully manipulated using SQL, Pro*C, OCI, and PL/SQL. The range of values for nested table subscripts is 1..2147483647. To extend a nested table, the built-in procedure EXTEND must be used. To delete elements, the built-in procedure DELETE must be used.

An uninitialized nested table is atomically null, so the IS NULL comparison operator can be used to see if a nested table is null. Oracle8 provides new operators such as CAST, THE, and MULTISET for manipulating nested tables.

Examples of Nested Tables

Example 1:
-------------

The following example illustrates how a simple nested table is created.

Varrays

Varrays are ordered groups of items of type VARRAY. Varrays can be used to associate a single identifier with an entire collection. This allows manipulation of the collection as a whole and easy reference of individual elements.

The maximum size of a varray needs to be specified in its type definition. The range of values for the index of a varray is from 1 to the maximum specified in its type definition. If no elements are in the array, then the array is atomically null. The main use of a varray is to group small or uniform-sized collections of objects.

Elements of a varray cannot be accessed individually through SQL, although they can be accessed in PL/SQL, OCI, or Pro*C using the array style subscript. The type of the element of a VARRAY can be any PL/SQL type except the following:

Varrays can be used to retrieve an entire collection as a value. Varray data is stored in-line, in the same tablespace as the other data in its row.

When a varray is declared, a constructor with the same name as the varray is implicitly defined. The constructor creates a varray from the elements passed to it. You can use a constructor wherever you can use a function call, including the SELECT, VALUES, and SET clauses.

A varray can be assigned to another varray, provided the datatypes are the exact same type. For example, suppose you declared two PL/SQL types:

Code: sql

TYPE My_Varray1 IS VARRAY(10) OF My_Type; TYPE My_Varray2 IS VARRAY(10) OF My_Type;

An object of type My_Varray1 can be assigned to another object of type My_Varray1 because they are the exact same type. However, an object of type My_Varray2 cannot be assigned to an object of type My_Varray1 because they are not the exact same type, even though they have the same element type.

Varrays can be atomically null, so the IS NULL comparison operator can be used to see if a varray is null. Varrays cannot be compared for equality or inequality.

Individual elements can be deleted from a nested table, but not from a varray. Therefore, nested tables can be sparse, whereas varrays are always dense.

Varrays are stored by Oracle in-line (in the same tablespace), whereas nested table data is stored out-of-line in a store table, which is a system-generated database table associated with the nested table.

When stored in the database, nested tables do not retain their ordering and subscripts, whereas varrays do.