12.2 Collection Types

During a traditional relational design process, one-to-many relationships, such as a department having many employees or an order consisting of many line items, are resolved as separate tables where the child table holds a foreign key to the parent table. In our example schema, each row in the line_item table knows which order it belongs to via a foreign key, but a row in the cust_order table does not directly know anything about line items. Beginning with Oracle8, such relationships can be internalized within the parent table using a collection. The two collection types available in Oracle8 and above are variable arrays, which are used for ordered, bounded sets of data, and nested tables, which are used for unordered, unbounded data sets.

12.2.1 Variable Arrays

Variable arrays, also called varrays, are arrays stored within a table. Elements of a varray must be of the same data type (although subtypes are permitted), are bounded by a maximum size, and are accessed positionally. Varrays may contain either a standard Oracle data type, such as DATE or VARCHAR2, or a user-defined object type. The following example illustrates the creation of a varray and its use as a column of a table:

CREATE TYPE resupply_dates AS VARRAY(100) OF DATE;


  part_nbr VARCHAR2(20) NOT NULL,

  name VARCHAR2(50) NOT NULL,

  supplier_id NUMBER(6),

  unit_cost NUMBER(8,2),

  inventory_qty NUMBER(6),

  restocks resupply_dates);

Along with descriptive information about the part, each row in the part_c table can hold up to 100 dates corresponding to when a shipment was received from the supplier.

Beginning with the Oracle Database 10g release, the maximum size of a varray can be modified after it has been defined, so you won't need to worry so much about how high to set the maximum size of your collections. To change the resupply_dates varray to hold 200 dates, you would issue the following:

ALTER TYPE resupply_dates MODIFY LIMIT 200;

12.2.2 Nested Tables

Like varrays, all elements of a nested table must be of the same data type, either a built-in data type or a user-defined type. Unlike varrays, however, nested tables do not have a maximum size and are not accessed positionally. The following example defines a nested table type containing an object type:

CREATE TYPE line_item_obj AS OBJECT (

  part_nbr VARCHAR2(20),

  quantity NUMBER(8,2),


CREATE TYPE line_item_tbl AS TABLE OF line_item_obj;

The to_string member function will be used later in the chapter for comparing instances of type line_item_obj.

Now that we have created a nested table type for line_item objects, we can choose to embed it into our cust_order table, as in the following:

CREATE TABLE cust_order_c (

  order_nbr NUMBER(8) NOT NULL,

  cust_nbr NUMBER(6) NOT NULL,

  sales_emp_id NUMBER(6) NOT NULL,

  order_dt DATE NOT NULL,

  sale_price NUMBER(9,2),

  order_items line_item_tbl)

NESTED TABLE order_items STORE AS order_items_table;

Using a nested table, we have absorbed an order's line items into the cust_order table, eliminating the need for the line_item table. Later in the chapter, you'll see how Oracle provides a way to detach the order_items collection when it is advantageous to do so.