-2

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.
Dunbay
  • 9
  • 1
  • 2
  • Not directly, but just the desire to do so implies the data types are compatible. If that's the case you could perhaps combine the queries with a UNION. Post the cursors and type definition of the collection. That may help provide a beer answer. – Belayer Dec 15 '17 at 02:03

1 Answers1

3

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;
/
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • but what if the cursor-A is having 100 rows ? do i need to combine 100 nested tables ? i don't think it is feasible. – Dunbay Dec 15 '17 at 14:36
  • The 100 rows can fit in a single nested table, and can be combined in one step. – Jon Heller Dec 15 '17 at 16:17
  • I'm sorry if i confused you but the requirement is to call cursor-B for each and every record in Cursor-A – Dunbay Dec 15 '17 at 21:04
  • @Dunbay In the cursor-A loop you can bulk collect cursor-B into a collection, and then combine that data with other collections. Maybe adding some pseudo-code would help me understand the issue. – Jon Heller Dec 15 '17 at 21:39