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)

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