Bulk Collect Twice over same nested table

Question

Is there any way that after the second bulk collect, data does not get override of the first bulk collect. I don't want to iterate in loop.

    DECLARE
       TYPE abc IS RECORD (p_id part.p_id%TYPE);

       TYPE abc_nt
       IS
          TABLE OF abc
             INDEX BY BINARY_INTEGER;

       v_abc_nt      abc_nt;
    BEGIN
       SELECT   p_id
         BULK   COLLECT
         INTO   v_abc_nt
         FROM   part
        WHERE   p_id IN ('E1', 'E2');

       SELECT   p_id
         BULK   COLLECT
         INTO   v_abc_nt
         FROM   part
        WHERE   p_id IN ('E3', 'E4');

       FOR i IN v_abc_nt.FIRST .. v_abc_nt.LAST
       LOOP
          DBMS_OUTPUT.put_line (
             'p_id is ' || v_abc_nt (i).p_id
          );
       END LOOP;
    END;

OUTPUT:

p_id is E3
p_id is E4

Note: E1 and E2 is present in part table.

score 12 · Accepted Answer · answered Nov 14 '12 at 16:05

You can't simply add the data to the collection, no.

You can, however, do a BULK COLLECT into a separate collection and then combine the collections assuming that you really just need/ want a nested table rather than an associative array...

DECLARE
   TYPE abc IS RECORD (p_id part.p_id%TYPE);

   TYPE abc_nt
   IS
      TABLE OF abc;

   v_abc_nt       abc_nt;
   v_abc_nt2      abc_nt;
BEGIN
   SELECT   p_id
     BULK   COLLECT
     INTO   v_abc_nt
     FROM   part
    WHERE   p_id IN ('E1', 'E2');

   SELECT   p_id
     BULK   COLLECT
     INTO   v_abc_nt2
     FROM   part
    WHERE   p_id IN ('E3', 'E4');

   v_abc_nt := v_abc_nt MULTISET UNION v_abc_nt2;

   FOR i IN v_abc_nt.FIRST .. v_abc_nt.LAST
   LOOP
      DBMS_OUTPUT.put_line (
         'p_id is ' || v_abc_nt (i).p_id
      );
   END LOOP;
END;

If you really want to use an associative array, you would need to write some code because there is no way for Oracle to know automatically how to remap the associations of one array when you combine it with another associative array that has some of the same keys.

I ran the same proc in my machine ,its giving me the following error:`wrong number or types of arguments in call to 'MULTISET_UNION_ALL'`,please tell me what is the issue with this — Gaurav Soni, Nov 14 '12 at 17:00
@GauravSoni - Did you change your code to use a nested table rather than an associative array as I suggested? If you still trying to use an associative array, you'll get that error. — Justin Cave, Nov 14 '12 at 17:04
:sorry i dint ,i will change it now.It is working fine thanks sir — Gaurav Soni, Nov 14 '12 at 17:05

score 0 · Answer 2 · answered Nov 09 '17 at 08:05

You can write it like this

bad example:

declare
  type t_numb is record(
    numb number);
  type t_numb_list is table of t_numb;
  v_numb_list t_numb_list;
begin
  with q as
   (select 1 a from dual union select 2 from dual union select 3 from dual)
  select q.a bulk collect into v_numb_list from q;
  with w as
   (select 4 a from dual union select 5 from dual union select 6 from dual)
  select w.a bulk collect into v_numb_list from w;

  for r in 1 .. v_numb_list.count loop
    dbms_output.put_line(v_numb_list(r).numb);
  end loop;
end;

and this works good:

declare
  type t_numb is record(
    numb number);
  type t_numb_list is table of t_numb;
  v_numb_list t_numb_list := t_numb_list();
  v_numb      t_numb;
begin
  for q in (select 1 a
              from dual
            union
            select 2
              from dual
            union
            select 3
              from dual) loop
    v_numb.numb := q.a;
    v_numb_list.extend;
    v_numb_list(v_numb_list.count) := v_numb;
  end loop;

  for w in (select 4 a
              from dual
            union
            select 5
              from dual
            union
            select 6
              from dual) loop
    v_numb.numb := w.a;
    v_numb_list.extend;
    v_numb_list(v_numb_list.count) := v_numb;
  end loop;

  for r in 1 .. v_numb_list.count loop
    dbms_output.put_line(v_numb_list(r).numb);
  end loop;
end;

Bulk Collect Twice over same nested table

2 Answers2