Oracle COLLECT Function

LISTAGG gathers the values of a group into one string. COLLECT gathers them into a collection instead: a nested table that SQL and PL/SQL can count, search, combine with multiset operators, or pass to a function. It is an aggregate function, used with GROUP BY like SUM or COUNT.

Code for This Guide

The main example is in the examples/misc-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

cast(collect([distinct | unique] column [order by expr]) as collection_type)

COLLECT returns a nested table of a system-generated type; CAST turns it into a named collection type that you can use elsewhere. DISTINCT removes duplicates and ORDER BY orders the elements. The examples use this type:

Setup:

create or replace type t_codes as table of varchar2(10);

Collect the Destinations of Each Airport

Example:

select origin, destinations, cardinality(destinations) as how_many
from   (select r.origin,
               cast(collect(cast(r.destination as varchar2(10)) order by r.destination)
                    as t_codes) as destinations
        from   routes r
        where  r.origin in ('SIN', 'SYD')
        group  by r.origin);

Output:

ORIGIN    DESTINATIONS          HOW_MANY
_________ __________________ ___________
SIN       [DXB, NRT, SYD]              3
SYD       [AKL, DXB, SIN]              3

Each airport gets a collection of its destinations, in alphabetical order, and CARDINALITY counts them. The inner CAST to varchar2(10) is not decoration; the next section shows why.

The Element Type Must Match

destination is a CHAR(3) column, so COLLECT builds a collection of CHAR(3), which cannot be cast to a table of VARCHAR2(10):

Example:

select cast(collect(r.destination) as t_codes) as destinations
from   routes r
where  r.origin = 'SIN';

Output:

Error starting at line : 1
In command -
select cast(collect(r.destination) as t_codes) as destinations
from   routes r
where  r.origin = 'SIN'
Error at Command Line : 1 Column : 21
Error report -
SQL Error: ORA-00932: expression ("SYS"."SYS_NT_COLLECT"("R"."DESTINATION")) is of data
type NIMBUS."ST00001RleNKe3J9/gYwIAEaxa/w=", which is incompatible with expected
data type NIMBUS.T_CODES

Cast each value to the element type first, as the working example does.

Distinct Values

DISTINCT inside COLLECT keeps one copy of each value, here the cabins each of three customers has booked.

Example:

-- the distinct cabins booked by each of three customers
select b.customer_id,
       cast(collect(distinct cast(t.cabin as varchar2(10))) as t_codes) as cabins
from   bookings b join tickets t on t.booking_id = b.booking_id
where  b.customer_id in (5, 12, 85)
group  by b.customer_id
order  by b.customer_id;

Output:

   CUSTOMER_ID CABINS
______________ ______________________
             5 [BUSINESS, ECONOMY]
            12 [BUSINESS, ECONOMY]
            85 [BUSINESS, ECONOMY]

Things to Know

• Use COLLECT when the next step needs a collection, for example a PL/SQL function that takes a list, or a multiset comparison such as a SUBMULTISET OF b.
• For display, LISTAGG or JSON_ARRAYAGG are simpler and need no type.
• Drop the type with drop type t_codes when you no longer need it.

Related Guides

• Oracle SQL Query to Use LISTAGG for String Aggregation
• Oracle CARDINALITY Function
• Oracle SET Function

Conclusion

COLLECT aggregates the values of a group into a nested table, which CAST turns into a named collection type. Match the element type exactly, cast the values first when it differs, and use DISTINCT and ORDER BY inside the call to shape the collection.