Specify the ITEM keyword in a collection subquery

When the projection list of the subquery contains a single expression, you can preface the projection list of the subquery with the ITEM keyword to specify that the element type of the MULTISET matches the data type of the subquery result. In other words, when you include the ITEM keyword, the database server does not put a row wrapper around the projection list. For example, if the subquery (that immediately follows the MULTISET keyword) returns INT values, the collection subquery is of type MULTISET(INT NOT NULL).

Suppose you create a function int_func() that accepts an argument of type MULTISET(INT NOT NULL). The following query shows a collection subquery that converts rows of INT values to a MULTISET and uses the collection subquery as an argument in the function int_func().

Figure 1: Query

EXECUTE FUNCTION int_func(MULTISET(SELECT ITEM int_col
   FROM tab1
   WHERE int_col BETWEEN 1 AND 10));

The query includes the ITEM keyword in the subquery, so the int_col values that the query returns are converted to a collection of type MULTISET (INT NOT NULL). Without the ITEM keyword, the collection subquery would return a collection of type MULTISET (ROW(a INT) NOT NULL).