how to append bulk fetched rows to a table type in oracle?

Question

I'm bulk collecting records from Cursor-B for every record in cursor-A, is there any way to append this Cursor-B data to a table type so that i can insert it into a table at once ?

• I cannot combine cursor-A and Cursor-B SQL.
• I know BULK COLLECT Never Appends to the Collection but is there any way to achieve what I'm mentioning.

Answer 1

You can combine collections in a single operation using a MULTISET UNION:

declare
    --Collection types.
    type type_rec is record(a number);
    type type_nt is table of type_rec;

    --Collection variables.
    v_var1 type_nt;
    v_var2 type_nt;
    v_both type_nt;
begin
    --Get the data.
    select 1 bulk collect into v_var1 from dual connect by level <= 1000;
    select 1 bulk collect into v_var2 from dual connect by level <= 1000;

    --Combine the two nested tables together in a single operation.
    v_both := v_var1 multiset union all v_var2;
end;
/