Subtleties – Part 3 (more workarounds for COLLECT DISTINCT in PL/SQL)

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL, and saw two workarounds – using dynamic SQL and using the SET function.
In Part 2 we saw that the SET function can operate on Nested Table but not on Varray.
In this post we’ll see two more workarounds.

Inline View

We can first remove the duplicates in an inline view, and then use the “simple” COLLECT function on the outcome of the inline view:

create type integer_vt as varray(100) of integer
/

var rc refcursor
begin
  open :rc for
    select person_id,cast(collect(project_id) as integer_vt) project_id_list
    from (
      select distinct person_id,project_id from project_assignments
    )
    group by person_id
    order by person_id;
end;
/

PL/SQL procedure successfully completed. 

print rc

PERSON_ID PROJECT_ID_LIST
---------- -----------------------------------
       101 INTEGER_VT(2, 3, 1)
       102 INTEGER_VT(2)
       103 INTEGER_VT(3)

MULTISET

We can use the MULTISET operator instead of the COLLECT function. Note that this solution is not identical to the previous ones – we use a separate query for each person as the input to the MULTISET operator, and we get results also for people with no project assignments at all:

begin
    open :rc for
        select p.person_id,
               cast(multiset (select distinct project_id
                     from   project_assignments a
                     where  a.person_id = p.person_id) as integer_vt) project_id_list
        from   people p
        order  by person_id;
end;
/

PL/SQL procedure successfully completed. 

print rc

PERSON_ID PROJECT_ID_LIST
---------- ------------------------------
       101 INTEGER_VT(1, 2, 3)
       102 INTEGER_VT(2)
       103 INTEGER_VT(3)
       104 INTEGER_VT()

Conclusion

There is no one “correct” solution. Under different circumstances, different solutions differ in complexity, performance, etc. We should use the one that is best for the specific case.
And if we need to use a collection type, then NESTED TABLE is by far my first choice. I would use the much more limited type VARRAY only if I have a really good reason to do so.