Java Programming with Oracle SQLJ

5.1. VARRAYs

A VARRAY is an ordered set of elements. The maximum number of elements in a VARRAY is set when the VARRAY is created. You create a VARRAY by first declaring a type, and then using that type to define a column in a table. The following example creates a VARRAY type, named t_address2, that contains three VARCHAR2 strings:

CREATE TYPE t_address2 AS VARRAY(3) OF VARCHAR2(50);
/

Each VARCHAR2 string in this type will be used to represent an address for a customer. Once you have created a VARRAY type, you can define a column that uses it. The following example creates a table named customers3, which contains a column named addresses that is based on the t_address2 type:

CREATE TABLE customers3 (
  id         NUMBER,
  first_name VARCHAR2(10),
  last_name  VARCHAR2(10),
  addresses  t_address2
);

In order to add a row containing a VARRAY to this table, you must place the elements for the VARRAY in a constructor for the VARRAY type. The following example adds a row to the customers3 table, placing two elements for the VARRAY in the constructor for the t_address2 type:

INSERT INTO customers3 VALUES (
  1, 'John', 'Smith',
  t_address2(
    '1 Anystreet, Anytown, CA, 12345',
    '2 Main Street, Big Town, NY, 54321'
  )
);

After this INSERT is performed, the customers3 table contains one row. The VARRAY stored in the addresses column contains two strings, one for each of the strings contained in the t_address2 constructor.

From the previous examples, you can see that the use of VARRAYs is similar to the ...