Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Would you be happy to sacrifice a FORALL for outer_tab ? –  FreshPhilOfSO Aug 14 '12 at 18:39
    
maybe I'll settle for two FORALLs: one for outer_tab and one for inner_tab? –  Isaac Kleinman Aug 14 '12 at 18:40
    
The problem is, I don't think it's possible to reference ind from a forall anywhere except INSERT/UPDATE/DELETE, therefore making forall not nestable in this situation. A loop instead of forall for outer_tab would work. I suspect you already knew this :) –  FreshPhilOfSO Aug 14 '12 at 18:46
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Wouldn't the third option be most efficient since all inserts are done by means of FORALLs; hence, less context switches? –  Isaac Kleinman Aug 15 '12 at 15:07
    
@Isaac Kleinman - You may be right. Let us know if you do some performance testing on it. –  Leigh Riffel Aug 15 '12 at 15:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.