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 table and populates the VARRAY with two t_address objects. Note the use of the t_address3 and t_address constructors:
INSERT INTO customers4 VALUES ( 1, 'John', 'Smith', t_address3( t_address('1 Anystreet', 'Anytown', 'CA', '12345'), t_address('2 Main Street', 'Big Town', 'NY', '54321') ) ); The t_address3 constructor contains two t_address objects. After the INSERT is performed, the customers4 table contains one row, and the VARRAY contains the two t_address objects specified in the t_address3 constructor.
Retrieving data from columns that are collections of objects is no different than retrieving data from any other collection column. As in the previous examples, you can just select the addresses column to view the elements stored in the VARRAY. For example:
SQL> SELECT * 2 FROM customers4 3 WHERE id = 1; ID FIRST_NAME LAST_NAME --- ---------- --------- ADDRESSES(STREET, CITY, STATE, ZIP) ----------------------------------- 1 John Smith T_ADDRESS3(T_ADDRESS('1 Anystreet', 'Anytown', 'CA', '12345'), T_ADDRESS('2 Main Street', 'Big Town', 'NY', '54321')) Notice that the values for the addresses column appear within a constructor for the t_address3 type, and that each object stored in the VARRAY appears within a t_address constructor.
