Skip to Main Content
  • Questions

  • Oracle bulk collect/ forall for dynamic usage

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prashanth.

Asked: October 05, 2016 - 5:35 pm UTC

Last updated: October 08, 2016 - 1:05 am UTC

Version: Oracle 12c

Viewed 10K+ times! This question is

You Asked


Hi Tom,

Thanks to all the great comments provided in the below link :

https://asktom.oracle.com/pls/apex/f?p=100:11:114010771330237::::P11_QUESTION_ID:3675533064673

I am currently looking for a solution to a problem in similar lines. Instead of fixed table , i need to be able to use the same procedure for multiple tables.
In the below example, if instead of TEMP_TABLE3 , i have to dynamically insert into TEMP_TABLE5 with all the column lists derived in PL/SQL variable , will it be possible to use forall statement ?

declare
  type l_array is table of TEMP_TABLE3%rowtype;
       l_rec_data l_array;
       l_sql      VARCHAR2(200);
       l_start    NUMBER:=1;
       j NUMBER;
 begin
        l_sql := 'select COL1,COL2,COL3,COL4,COL5 FROM TEMP_TABLE1';
        execute immediate l_sql bulk collect into l_rec_data;
        DBMS_OUTPUT.PUT_LINE(l_rec_data.COUNT);
        begin 
        loop 
         begin 
          forall i in l_start .. l_rec_data.count 
          insert into TEMP_TABLE3 (COL1,COL2,COL3,COL4,COL5) values ( l_rec_data(i).COL1,l_rec_data(i).COL2,l_rec_data(i).COL3,l_rec_data(i).COL4,l_rec_data(i).COL5); 
          EXIT; 
          exception 
            when DUP_VAL_ON_INDEX THEN
            dbms_output.put_line( 'DUP_VAL_ON_INDEX row index = ' || (l_start+sql%rowcount) ||  ' ' || sqlerrm ); 
            j:=l_start+sql%rowcount;
            UPDATE TEMP_TABLE3 SET COL2=l_rec_data(j).COL2, COL3=l_rec_data(j).COL3, COL4=l_rec_data(j).COL4, COL5=l_rec_data(j).COL5 WHERE COL1=l_rec_data(j).COL1;
            l_start := l_start + 1; 
            when others then 
            dbms_output.put_line( 'Bad row index = ' || (l_start+sql%rowcount) ||  ' ' || sqlerrm ); 
            l_start := l_start + sql%rowcount + 1; 
          end; 
         end loop; 
         end;
END;

and Connor said...

Well...first of all, time moves on and features change

Check out this link

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9524231800346013638

DML error logging might make this a whole lot easier for you.

But if you want to stay with PLSQL, then dynamic SQL is support within forall, eg

SQL> create or replace
  2  procedure blah(p_tname varchar2, p_col1 varchar2, p_col2 varchar2) is
  3    type str_list is table of varchar2(10) index by pls_integer;
  4    l_c1 str_list;
  5    l_c2 str_list;
  6  begin
  7    l_c1(1) := 'A';
  8    l_c1(2) := 'B';
  9    l_c1(3) := 'C';
 10
 11
 12    l_c2(1) := 'X';
 13    l_c2(2) := 'Y';
 14    l_c2(3) := 'Z';
 15
 16    forall i in 1 .. 3
 17      execute immediate 'insert into '||p_tname||'('||p_col1||','||p_col2||') values (:1, :2)'
 18      using l_c1(i), l_c2(i);
 19  end;
 20  /

Procedure created.

SQL>
SQL> drop table t1 purge;

Table dropped.

SQL> drop table t2 purge;

Table dropped.

SQL>
SQL> create table t1(x1 varchar2(10), x2 varchar2(10));

Table created.

SQL> create table t2(y1 varchar2(10), y2 varchar2(10));

Table created.

SQL>
SQL> exec blah('t1','x1','x2');

PL/SQL procedure successfully completed.

SQL> exec blah('t2','y1','y2');

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t1;

X1         X2
---------- ----------
A          X
B          Y
C          Z

SQL> select * from t2;

Y1         Y2
---------- ----------
A          X
B          Y
C          Z


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

forall execute immediate using bind

Prashanth, October 06, 2016 - 7:42 am UTC

Hi Connor,

Thanks for your reply. Furthermore, if i have the binds in a variable , how can i execute immediate :
   lv_using_clause:='l_c1(i), l_c2(i)';
   forall i in 1 .. 3
     execute immediate 'insert into '||p_tname||'('||p_col1||','||p_col2||') values (:1, :2)'
     using 'lv_using_clause';


I get the below error if tried like above :

PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

Thanks for your assistance.

Regards.
Connor McDonald
October 06, 2016 - 1:48 pm UTC

If you have an *unknown* number of binds, then you are into dbms_sql territory.

If you have "known unknown" number of binds, ie, it is (say) between 1 and 10, you could code accordingly, ie

if bind_count = 1 then
  forall   
     execute immediate '..' using :1
elsif bind_count = 1 then
  forall   
     execute immediate '..' using :1, :2


etc etc

Prashanth, October 07, 2016 - 9:39 am UTC

Hi Connor,

I tried to use DBMS_SQL to try binding variables like below :


l_sql := 'select COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15 FROM TEMP_TABLE';
execute immediate l_sql bulk collect into l_rec_data ;

bulk_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(bulk_cursor, 'begin' || chr(10) ||
        'begin  '|| chr(10) ||
        'loop '|| chr(10) ||
         'begin '|| chr(10) ||
          'forall i in :l_start .. :l_rec_data.count' || chr(10) ||
          'execute immediate :lv_insert_sql using :lv_temp_col_name;'|| chr(10) ||
          'EXIT; '|| chr(10) ||
          'exception '|| chr(10) ||
            'when DUP_VAL_ON_INDEX THEN '|| chr(10) ||
            'dbms_output.put_line( ''DUP_VAL_ON_INDEX row index = '' || (l_start+sql%rowcount) ||  '' '' || sqlerrm );' || chr(10) ||

            'j:=l_start+sql%rowcount;' || chr(10) ||
            'execute immediate :lv_update_sql USING j; '|| chr(10) ||
            'l_start := l_start + 1; '|| chr(10) ||
            'when others then '|| chr(10) ||
            'dbms_output.put_line( ''Bad row index = '' || (l_start+sql%rowcount) ||  '' '' || sqlerrm );' || chr(10) ||
            'l_start := l_start + sql%rowcount + 1;' || chr(10) ||
          'end; '|| chr(10) ||
         'end loop; '|| chr(10) ||
         'end; '|| chr(10) ||
'END; ', dbms_sql.native);
dbms_sql.bind_variable(bulk_cursor, ':l_start', l_start);
dbms_sql.bind_variable(bulk_cursor, ':l_rec_data.count', l_rec_data.count);
dbms_sql.bind_variable(bulk_cursor, ':lv_insert_sql', lv_insert_sql);
dbms_sql.bind_variable(bulk_cursor, ':lv_temp_col_name', lv_temp_col_name);
dbms_sql.bind_variable(bulk_cursor, ':lv_update_sql', lv_update_sql);
dummy := dbms_sql.execute(bulk_cursor);
dbms_sql.close_cursor(bulk_cursor);



However , i still get the error .. DML statement without BULK In-BIND cannot be used inside FORALL

Please help.
Connor McDonald
October 08, 2016 - 1:05 am UTC

"DML statement without BULK In-BIND cannot be used inside FORALL"

Put aside the dynamic SQL part for a sec, this is just a normal error for FORLAL

forall i in 1 .. 10
insert into t values ( 10 );

or

forall i in 1 .. 10
insert into t values ( l_variable );

would not work because nothing in the insert is being bulk-bound. You need to be bulk-binding *something* , ie

forall i in 1 .. 10
insert into t values ( l_my_array(i) );



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library