Subtleties – Part 1 (SQL and PL/SQL)

Note: I wrote this post in May 2016, when the latest released version of Oracle was 12.1.
As of Oracle 12.2 COLLECT DISTINCT is supported in PL/SQL.

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.

To demonstrate it I’ll use the PROJECT_ASSIGNMENTS table, which contains assignments of people to projects. The same person may be assigned to the same project more than once, in different times.

create table people (
       person_id integer not null constraint people_pk primary key,
       first_name varchar2(20) not null,
       last_name varchar2(30) not null
);

create table projects (
       project_id integer not null constraint projects_pk primary key,
       project_name varchar2(100) not null
);

create table project_assignments (
       assignment_id integer not null constraint project_assignments_pk primary key,
       person_id integer not null constraint assignments_fk_people references people,
       project_id integer not null constraint assignments_fk_projects references projects,
       from_date date,
       to_date date
);

break on person_id dup skip 1

select person_id,project_id,from_date,to_date
from   project_assignments
order  by person_id,from_date;


 PERSON_ID PROJECT_ID FROM_DATE  TO_DATE
---------- ---------- ---------- ----------
       101          1 01/01/2016 10/01/2016
       101          2 11/01/2016 20/01/2016
       101          3 21/01/2016 31/01/2016
       101          1 01/02/2016 10/02/2016

       102          2 05/01/2016 15/01/2016
       102          2 05/02/2016 25/02/2016
       102          2 01/03/2016 02/03/2016

       103          3 12/01/2016 13/01/2016


8 rows selected.

All the following examples have been tested in 11.2.0.4 and 12.1.0.2.

The COLLECT function returns a collection of elements. For example, the following query returns all the project assignments per person, using a nested table type:

create type integer_ntt as table of integer
/

select person_id,cast(collect(project_id) as integer_ntt) project_id_list 
from project_assignments 
group by person_id
order by person_id;

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

We can execute the same statement in PL/SQL:

var rc refcursor
begin
  open :rc for
    select person_id,cast(collect(project_id) as integer_ntt) project_id_list
    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_NTT(1, 1, 3, 2)
       102 INTEGER_NTT(2, 2, 2)
       103 INTEGER_NTT(3)

Now, let’s change the query to get a distinct list of the projects for each person, using the DISTINCT option of the COLLECT function:

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;

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

No problems here. But if we try to use the exact same query in PL/SQL, it will fail:

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

One workaround we can use is using dynamic SQL instead of static SQL. This workaround usually (but not always) works in cases where valid SQL syntax is not supported by the PL/SQL engine. Note that this time the SQL statement is passed as a string literal to the OPEN statement (so the PL/SQL engine does not really “understand” it).

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)

Another workaround is to apply the SET function on the non-unique result of the COLLECT function:

begin
  open :rc for
    select person_id,set(cast(collect(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, 3, 2)
       102 INTEGER_NTT(2)
       103 INTEGER_NTT(3)

In the next post we’ll see a very similar use case, in which the SET function cannot be used, and some other workarounds.