How to populate a PL/SQL Object Type with encapsulated Object Types or Collection Types using a single SQL Query

Question

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?

Answer 1

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 )