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