COLLECT DISTINCT in PL/SQL Works in Oracle 12.2

About a year ago I wrote the post Subtleties – Part 1 (SQL and PL/SQL). I wrote there:

Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement.
One example is the COLLECT aggregate function with the DISTINCT option.

And I showed an example that was executed in 11.2.0.4 and in 12.1.0.2.

Today I tried it in 12.2.0.1, and I was very pleased to see that now COLLECT DISTINCT is working also in PL/SQL.
Using the same example from the original post:

In 12.1.0.2:

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

    select person_id,cast(collect(distinct project_id) as integer_ntt) project_id_list
                          *
ERROR at line 3:
ORA-06550: line 3, column 27:
PL/SQL: ORA-30482: DISTINCT option not allowed for this function
ORA-06550: line 3, column 5:
PL/SQL: SQL Statement ignored

In 12.2.0.1:

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

print rc

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

One thought on “COLLECT DISTINCT in PL/SQL Works in Oracle 12.2”

  1. Hi Oren,

    If you already mentioned the COLLECT function …
    I just remembered that in 11gR2 using this function always
    “left behind” many persistent collection types created internally,
    on-the-fly, on each usage of this function.

    As far as I remember, this problem was raised by that time to Oracle support and it was considered as a bug, that is,
    we would expect those collection types to be cleaned-up
    at some point later on, either at the end of the session
    that created them or, maybe, when the cursor was aged out from the Library Cache.

    I wonder whether this problem was also corrected in 12c,
    in any of its releases.

    Cheers & Best Regards,
    Iudith

Leave a Reply

Your email address will not be published. Required fields are marked *