Is it possible to insert a table type collection within a table collection in PL/SQL

I have a requirement to send back a record type in a table collection which have 2 fields-

1. Line_id
2. Fulfillment_set_id

A line_id is always unique but a line_id can have more than one Fulfillment_set_id. So I have created a record type

Type fulfillment_set_id_tbl is Table of type NUMBER;

fulfillment_set_id_rec_tbl fulfillment_set_id_tbl := fulfillment_set_id_tbl(); 

Type line_rec is Record (
line_id NUMBER,
fulfillment_set_id fulfillment_set_id_tbl 
);

Is the above collection structure possible or am I missing something because I am not able to get success with it. And how do I insert data into it.

Answer

For a single record, try this:

declare
    type fulfillment_set_id_tbl is table of number;

    type line_rec is record
        ( line_id number
        , fulfillment_set_id fulfillment_set_id_tbl );

    r line_rec;
begin
    r.line_id := 1;
    r.fulfillment_set_id := fulfillment_set_id_tbl(10,11,12);
end;

For a table of line recs:

declare
    type fulfillment_set_id_tbl is table of number;

    type line_rec is record
        ( line_id number
        , fulfillment_set_id fulfillment_set_id_tbl );

    type line_rec_tbl is table of line_rec;

    r line_rec;
    line_recs line_rec_tbl := new line_rec_tbl();
begin
    r.line_id := 1;
    r.fulfillment_set_id := fulfillment_set_id_tbl(10,11,12);

    line_recs.extend;
    line_recs(1) := r;
end;

From Oracle 18c we get pseudo-constructors for record types (called ‘Qualified Expressions’), so you can populate them declaratively:

declare
    type fulfillment_set_id_tbl is table of number;

    type line_rec is record
        ( line_id number
        , fulfillment_set_id fulfillment_set_id_tbl );

    type line_rec_tbl is table of line_rec;

    line_recs line_rec_tbl :=
        new line_rec_tbl
        ( line_rec(1, fulfillment_set_id_tbl(10,11,12))
        , line_rec(2, fulfillment_set_id_tbl(13,14,14))
        );
begin
    dbms_output.put_line(line_recs(2).fulfillment_set_id(3));
end;

Output:

14

If you were to create the types as standalone objects in SQL, that would give you even more flexibility with custom constructors, and the ability to use them in queries.

By the way, the question title refers to collections of collections, but the main issue here seemed to be the PL/SQL record. Hopefully this has answered both, but let me know if it hasn’t.

To achieve the bulk collect mentioned in comments, you will need a collection type defined in SQL, for example:

create or replace type number_tt as table of number;

Then it should work:

declare
    type line_rec is record
        ( line_id number
        , fulfillment_set_id number_tt );

    type line_rec_tbl is table of line_rec;

    line_recs line_rec_tbl;
begin
    with demo (line_id, fulfillment_set_id) as
         ( select 1, 10 from dual union all
           select 1, 11 from dual union all
           select 1, 12 from dual union all
           select 2, 13 from dual union all
           select 2, 14 from dual union all
           select 2, 15 from dual )
    select line_id, cast(collect(fulfillment_set_id) as number_tt)
    bulk collect into line_recs
    from demo
    group by line_id;
    
    dbms_output.put_line('line_recs contains ' || line_recs.count || ' records');
end;

Output:

line_recs contains 2 records