Inserting Values into Collection Columns (IDS)

You can use the VALUES clause to insert values into a collection column. For more information, see Collection Constructors.

For example, suppose you define the tab1 table as follows:

CREATE TABLE tab1 
(
   int1 INTEGER, 
   list1 LIST(ROW(a INTEGER, b CHAR(5)) NOT NULL),
   dec1 DECIMAL(5,2)
)

The following INSERT statement inserts a row into tab1:

INSERT INTO tab1 VALUES 
   (
   10,
   LIST{ROW(1,'abcde'), 
      ROW(POW(3,3), '=27'), 
      ROW(ROUND(ROOT(126)), '=11')},
   100
   )

The collection column, list1, in this example, has three elements. Each element is an unnamed row type with an INTEGER field and a CHAR(5) field. The first element is composed of two literal values, an integer (1) and a quoted string (abcde). The second and third elements also use a quoted string to indicate the second field, but specify the value for the first field with an expression.

Regardless of what method you use to insert values into a collection column, you cannot insert NULL elements into the column. Thus expressions that you use cannot evaluate to NULL. If the collection that you are attempting to insert contains a NULL element, the database server returns an error.

You can also use a collection variable to insert the values of one or more collection elements into a collection column. For more information, see Collection-Derived Table.