Insert NULL values into a collection that contains a row type

To insert values into a collection that is a ROW type, you must specify a value for each field in the ROW type.

In general, NULL values are not allowed in a collection. However, if the element type of the collection is a ROW type, you can insert NULL values into individual fields of the row type.

You can also specify an empty collection. An empty collection is a collection that contains no elements. To specify an empty collection, use the braces ({}). For example, the following statement inserts data into a row in the manager table but specifies that the direct_reports and projects columns are empty collections:

INSERT INTO manager
   VALUES ('Sayles', 'marketing', "SET{}", 
   "LIST{ROW(NULL, SET{})}"
);

A collection column cannot contain NULL elements. The following statement returns an error because NULL values are specified as elements of collections:

INSERT INTO manager
   VALUES ('Cole', 'accounting', "SET{NULL}",
   "LIST{ROW(NULL, ""SET{NULL}"")}"

The following syntax rules apply for performing inserts and updates on collection types:

Use braces ({}) to demarcate the elements that each collection contains.
If the collection is a nested collection, use braces ({}) to demarcate the elements of both the inner and outer collections.