Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
COLLECT
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The COLLECT() aggregate function is Oracle's vendor specific version of the standard SQL ARRAY_AGG function. It produces a structurally typed array, which is implemented behind the scenes as a nominally typed, system-generated array. It supports being used with an ORDER BY clause.
The following example is using an auxiliary data type and casting the COLLECT() result to that type.
CREATE TYPE NUMBERS AS TABLE OF NUMBER(10); SELECT CAST(collect(ID ORDER BY ID) AS NUMBERS); FROM BOOK;
create.select(
collect(BOOK.ID, NumbersRecord.class).orderBy(BOOK.ID))
.from(BOOK)
Producing:
+--------------+ | collect | +--------------+ | [1, 2, 3, 4] | +--------------+
Feedback
Do you have any feedback about this page? We'd love to hear it!