Determine when to use Collections and Variable Size Arrays

A collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position within the collection. Collections work like arrays that you find in most third-generation programming languages. However, collections can have only one dimension and must be indexed by integers. In some languages such as Ada and Pascal, arrays can have multiple dimensions and can be indexed by enumeration types. Collections can store instances of an object type and, conversely, can be attributes of an object type. You can also pass collections as parameters.Therefore, you can use them to move columns of data in and out of database tables or between client-side applications and stored subprograms. Furthermore, you can define collection types within a PL/SQL package, then use them programmatically within your applications.

Oracle supports two types of collections:

1. variable size arrays and
2. nested tables.

Enumeration: An enumeration is a data type which can be given a finite (usually small) set of named values.
Therefore, enumerations are useful for representing real world values that naturally have a finite set of values, each of which has a meaningful name. You will learn about nested tables in the next lesson. Let us concentrate on variable size arrays.

A variable size array, or varray, is a single dimension collection of homogenous^[1] elements. They are always bounded (have an upper limit to the number of elements within the array) and never sparse^[2] (have nonconsecutive subscripts). Each element has a position that uniquely identifies the element within the varray. A position is an integer ranging from one to the maximum number of elements that are declared within the varray. A user-defined data type may be declared as a varray of an object type or some scalar data type. The following example declares PHONE_ARRAY as a varray. Any element of type PHONE_ARRAY is a varray of up to 10 telephone numbers. Each number is represented by a data item of type VARCHAR2:

CREATE TYPE PHONE_ARRAY AS 
VARRAY (10) OF VARCHAR2(15);

In this case, the varray represents a list of contact phone numbers for a single customer. For this situation, a varray is a better choice than a nested table for the following reasons:

1. The order of the numbers might be important. Varrays are ordered, whereas nested tables are unordered.
2. The number of phone numbers for a specific customer is small. Varrays force you to specify a maximum number of elements (10 in this case) in advance. Varrays use storage more efficiently than nested tables, which have no special size limitations.
3. There is no reason to query the phone number list, so the nested table representation offers no benefit.

After creating a varray, you must associate it with an object. The following example defines CUSTOMER_TYPE, which includes PHONE_ARRAY as an attribute:

CREATE OR REPLACE TYPE PETSTORE.CUSTOMER_TYPE 
AS OBJECT
(CUST_ID NUMBER(10),
 FIRST_NAME VARCHAR2(20),
 LAST_NAME VARCHAR2(25),
 FULL_ADDRESS ADDRESS_TYPE,
 PHONE_LIST PHONE_ARRAY,
 LAST_UPDATE DATE,
 UPDATED_BY_USER VARCHAR2(15)
)

[1]Homogenous: This term means the elements are of the same data type.

[2]Sparse: Sparse means lesser number of elements or records.