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
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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!