Java Programming with Oracle SQLJ

5.3. Storing an Object Type in a Collection

You are not limited to storing just the basic Oracle types in a collection. You can also store object types. If you want to store objects in a collection, you must define the collection type as containing elements of the relevant object type (of course, the object type must already exist before creating the collection type). The examples in this section use the t_address object type described in Chapter 4. Just as a reminder, the t_address object type is used to store addresses and was defined using the following CREATE TYPE statement:

CREATE TYPE t_address AS OBJECT (
  street VARCHAR2(15),
  city   VARCHAR2(15),
  state  CHAR(2),
  zip    VARCHAR2(9)
);
/

To create a collection type that stores t_address objects, the element type of the collection should be t_address. The following example creates a VARRAY type, named t_address3, that stores t_address elements:

CREATE TYPE t_address3 AS VARRAY(3) OF t_address;
/

In the same manner, nested table types whose elements are object types can also be created. Once a collection type is created, it can be used to define columns in a table. The following example creates a table, named customers4, which contains a column named addresses of type t_address3:

CREATE TABLE customers4 (
  id         NUMBER,
  first_name VARCHAR2(10),
  last_name  VARCHAR2(10),
  addresses  t_address3
);

The customers4 table now contains a column named addresses that is a VARRAY of t_address objects. The following example adds a row to the customers4 ...