FORALL on Nested Collections

Question

Suppose I have the following two tables in my Oracle database:

create table a (num number);
create table b (val varchar2(30);

and in my PL/SQL program I have declared a nested collection as follows:

declare
        -- type for inner collection; shadows table b
        type inner_tab_t is table of varchar2(30);

        -- record type for outer table
        type outer_tab_rec is record
        (
            num  number,
            vals inner_tab_t
        );

        -- type for outer table
        type outer_tab_t is table of outer_tab_rec;

        -- actual inner_tab_t
        inner_tab inner_tab_t;

        -- actual outer_tab_t
        outer_tab outer_tab_t;

I am wondering whether it is possible to use a FORALL statement to insert the data from outer_tab into both tables a and b?

In other words, given the following assignments:

begin
        inner_tab := inner_tab_t('one', 'two', 'three');

        outer_tab := outer_tab_t();

        outer_tab.extend;

        outer_tab(outer_tab.last).num := 1;
        outer_tab(outer_tab.last).vals := inner_tab;

I'd like to do something like this (obviously doesn't work):

 forall ind in outer_tab.first .. outer_tab.last
            insert into a (num)
                values(outer_tab(ind).num)
                forall ind2 in outer_tab(ind).vals.first .. outer_tab(ind).vals.last
                    insert into b (val)
                        values (outer_tab(ind).vals(ind2);

Is there a convenient way of handling such a situation?

Answer 1

It is possible to do this in a single FORALL statement, provided you create the nested tables as SQL objects. This allows you to reference them as tables with the SQL. You can then use INSERT ALL to do a multi-table insert:

create or replace type t_inner_tab_t as table of varchar2(30); 
/

create or replace type outer_tab_t as object (
  num number,
  vals  t_inner_tab_t
);
/

create or replace type t_outer_tab_t is table of outer_tab_t;
/

declare
  inner_tab t_inner_tab_t;
  outer_tab t_outer_tab_t;
begin
    inner_tab := t_inner_tab_t('one', 'two', 'three');

    outer_tab := t_outer_tab_t();

    outer_tab.extend;

    outer_tab(outer_tab.last) := outer_tab_t(1, inner_tab);

    forall i in outer_tab.first .. outer_tab.last 
      insert all
        when r = 1 then
          into a (num) values (o)
        when r  >= 1 then
          into b (val) values (c)
        select column_value c, outer_tab(i).num o, 
               row_number() over (partition by outer_tab(i).num order by outer_tab(i).num) r 
        from   table(cast(outer_tab(i).vals as t_inner_tab_t));

end;
/

SELECT * FROM a;

NUM
---
  1 

SELECT * FROM b;


VAL                          
------------------------------
one                            
two                            
three      

The row_number() clause is to enable the conditional insert (when r = 1). Without this, you'll insert into a for every value present in your outer_tab.

Answer 2

As of 11.2 there is no support for nested ForAlls without the use of an EXECUTE IMMEDIATE block and the inner type being an SQL type (see this). As far as I can tell, your options are these:

• A For Loop Inside a For Loop

  For vOuterLoop In outer_tab.first .. outer_tab.last Loop
     INSERT INTO a(num) VALUES (outer_tab(vOuterLoop).num);
     inner_tab := outer_tab(vOuterLoop).vals;
     For vInnerLoop In inner_tab.first .. inner_tab.last Loop
        INSERT INTO b(val) VALUES (inner_tab(vInnerLoop));
     End Loop;
  End Loop;
  

• A ForAll Inside a For Loop

  For vOuterLoop In outer_tab.first .. outer_tab.last Loop
     INSERT INTO a(num) VALUES (outer_tab(vOuterLoop).num);
     inner_tab := outer_tab(vOuterLoop).vals;
     ForAll vInnerLoop In inner_tab.first .. inner_tab.last 
        INSERT INTO b(val) VALUES (inner_tab(vInnerLoop));
  End Loop;
  

• A ForAll then a ForAll Inside a For Loop

  ForAll vOuterLoop In outer_tab.first .. outer_tab.last
     INSERT INTO a(num) VALUES (outer_tab(vOuterLoop).num);        
  For vOuterLoop In outer_tab.first .. outer_tab.last Loop
     inner_tab := outer_tab(vOuterLoop).vals;
     ForAll vInnerLoop In inner_tab.first .. inner_tab.last 
        INSERT INTO b(val) VALUES (inner_tab(vInnerLoop));
  End Loop;
  

You might classify the third as simply a less efficient variation of the second, but it does technically "use a FORALL statement to insert the data". The second variation is probably your best option.