COLLECT

Syntax

Description of the illustration collect.gif

Purpose

COLLECT is an aggregate function that takes as its argument a column of any type and creates a nested table of the input type out of the rows selected. To get accurate results from this function you must use it within a CAST function.

If column is itself a collection, then the output of COLLECT is a nested table of collections. If column is of a user-defined type, then column must have a MAP or ORDER method defined on it in order for you to use the optional DISTINCT, UNIQUE and ORDER BY clauses.

Examples

The following example creates a nested table from the varray column of phone numbers in the sample table oe.customers:

CREATE TYPE phone_book_t AS TABLE OF phone_list_typ;
/
SELECT CAST(COLLECT(phone_numbers) AS phone_book_t) Phone_Book 
   FROM customers
   ORDER BY phone_book;