FORALL on Nested Collections

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?

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.

StackExchange Database Administrators Q#22475, answer score: 3