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.