Java Programming with Oracle SQLJ

5.2. Nested Tables

A nested table is a set of elements that may be stored, or nested, within another table. There are two advantages that a nested table has over a VARRAY:

• A nested table doesn't have a fixed size, whereas a VARRAY has an upper bound specified in the CREATE TYPE statement.

• You can insert, update, and delete individual elements in a nested table, whereas with a VARRAY, you must recreate the entire VARRAY to change even a single element.

However, there are two advantages that a VARRAY has over a nested table:

• A VARRAY occupies less space in the database than a nested table.

• A VARRAY is stored with the other columns in a row, whereas a nested table is stored in a separate table. This means that access to the elements in a VARRAY can be faster than for a nested table.

As with VARRAY types, you use the CREATE TYPE statement to create nested table types. The following example creates a nested table type named t_address4 to hold a list of addresses, with each address being stored as a VARCHAR2 string:

CREATE TYPE t_address4 AS TABLE OF VARCHAR2(50);
/

Because nested tables are stored separately from the table in which the nested table is contained, you must use the NESTED TABLE clause in your CREATE TABLE statement. The NESTED TABLE clause has the following syntax:

CREATE TABLE table_name (
...
)
NESTED TABLE
  column_name
STORE AS
  nested_table_name;

The syntax elements are as follows:

table_name

Specifies the name of the table you are creating. This is the outer ...