I am currently setting up a data model using nested Object Types and Collection Types within a parent Object Type and need a way to populate the Object Type including the child objects using a single SQL Query.
As an example using the HR schema I created an example data structure. Goal is it to get a collection of all departments containing a list of the employees working in said department.
Using Oracle SQL Database 12.1 I have created the data structure:
CREATE OR REPLACE TYPE employee_ot AS OBJECT (
employee_id NUMBER,
first_name VARCHAR2(100),
last_name VARCHAR2(100)
)
create or replace TYPE EMPLOYEE_CT AS TABLE OF employee_ot;
create or replace TYPE DEPARTMENT_OT AS OBJECT
(
department_id number,
employees employee_ct
)
create or replace TYPE DEPARTMENT_CT AS TABLE OF DEPARTMENT_OT;
Simply populating the employee_ct works using:
DECLARE
v_employee_ct employee_ct;
BEGIN
SELECT
employee_ot(employee_id,first_name,last_name)
BULK COLLECT INTO
v_employee_ct
FROM
emp_details_view;
dbms_output.put_line(v_employee_ct.count);
FOR employee_index IN v_employee_ct.first..v_employee_ct.last LOOP
dbms_output.put_line(v_employee_ct(employee_index).employee_id
|| ' '
|| v_employee_ct(employee_index).first_name
|| ' '
|| v_employee_ct(employee_index).last_name);
END LOOP;
END;
When I try to populate the department_ct using a similar query, I get an error:
DECLARE
v_department_ct department_ct;
BEGIN
select department_ot(ev1.department_id,
(SELECT
employee_ot(ev2.employee_id,ev2.first_name, ev2.last_name)
BULK COLLECT INTO
v_employee_ct
FROM
emp_details_view ev2
where ev2.department_id = ev1.department_id))
bulk collect into v_department_ct
from emp_details_view ev1
group by department_id;
END;
PL/SQL: ORA-01744: inappropriate INTO
Removing the second bulk collect into clause I get:
DECLARE
v_department_ct department_ct;
BEGIN
select department_ot(ev1.department_id,
(SELECT
employee_ot(ev2.employee_id,ev2.first_name, ev2.last_name)
FROM
emp_details_view ev2
where ev2.department_id = ev1.department_id))
bulk collect into v_department_ct
from emp_details_view ev1
group by department_id;
END;
PL/SQL: ORA-00932: inconsistent datatypes: expected HR.EMPLOYEE_OT got HR.EMPLOYEE_CT
Using a function to get the employee_ct for a specified department_id works. However I fear performance issues when this is used on a big dataset:
CREATE OR REPLACE FUNCTION get_employees (
department_id_in NUMBER
) RETURN employee_ct AS
v_employee_ct employee_ct;
BEGIN
v_employee_ct := employee_ct ();
SELECT
employee_ot(employee_id,first_name,last_name)
BULK COLLECT INTO
v_employee_ct
FROM
emp_details_view
WHERE
department_id = department_id_in;
RETURN v_employee_ct;
END get_employees;
DECLARE
v_department_ct department_ct;
BEGIN
SELECT
department_ot(ev1.department_id, (get_employees(ev1.department_id) ) )
BULK COLLECT INTO
v_department_ct
FROM
emp_details_view ev1
GROUP BY
department_id;
FOR department_index IN v_department_ct.first..v_department_ct.last LOOP
DECLARE
v_department_ot department_ot;
BEGIN
v_department_ot := v_department_ct(department_index);
dbms_output.put_line(v_department_ot.department_id || ' ' ||v_department_ot.employees.count);
FOR employee_index IN v_department_ot.employees.first..v_department_ot.employees.last LOOP
DECLARE
v_employee_ot employee_ot;
BEGIN
v_employee_ot := v_department_ot.employees(employee_index);
dbms_output.put_line(v_employee_ot.employee_id
|| ' '
|| v_employee_ot.first_name
|| ' '
|| v_employee_ot.last_name);
END;
END LOOP;
END;
END LOOP;
END;
Is there any way to avoid using functions and rather write the creation of encapsulated objects in a single query?
You can use multiset
operator and cast data as employee_ct
. This code block worked for me:
declare
v_department_ct department_ct;
begin
select department_ot(ev1.department_id,
cast(multiset(select employee_ot(ev2.employee_id,ev2.first_name, ev2.last_name)
from emp_details_view ev2
where ev2.department_id = ev1.department_id )
as employee_ct))
bulk collect into v_department_ct
from emp_details_view ev1
group by department_id;
end;
My test data:
create table emp_details_view (department_id, employee_id, first_name, last_name) as (
select 1, 101, 'A', 'A' from dual union all
select 1, 102, 'B', 'B' from dual union all
select 2, 201, 'X', 'X' from dual )