Does anyone know if its possible to use Bulk insert from following code:
FORALL I IN IBT_KONTIDS.FIRST .. IBT_KONTIDS.LAST
INSERT INTO EX_TABLE VALUES (IBT_KONTIDS(I), IBT_PROJNUMS(I), CURRENTUSER, SYSDATE);
What I am trying to achieeve is a fast insert into EX_TABLE from my 2 arrays and additional parameters like userinfo and current time. Thanks in advance !
I don't think so. But, even if you could why would you want to? You're asking for more trouble than I could possibly name. What happens if your two arrays don't have the same number of records in them? What happens if they were collected into the array in a different order?
Why not just use a join collect everything into 1 cursor and then insert that?
SQL>
SQL> create table blah as
2 select a.*
3 , cast( null as varchar2(30) ) as usr
4 , cast( null as date ) as dt
5 , cast( null as varchar2(30) ) as object_name
6 from user_tables a
7 where 1 = 0;
Table created.
SQL>
SQL> declare
2
3 cursor c_tab is
4 select a.*, user, sysdate, b.object_name
5 from user_tables a
6 join user_objects b
7 on a.table_name = b.object_name
8 ;
9
10 type t__tab is table of c_tab%rowtype index by binary_integer;
11 t_tab t__tab;
12
13 begin
14
15 open c_tab;
16
17 loop
18 fetch c_tab bulk collect
19 into t_tab limit 1000;
20
21 exit when t_tab.count = 0;
22
23 forall ii in t_tab.first .. t_tab.last
24 insert into blah
25 values t_tab(ii)
26 ;
27
28 commit;
29
30 end loop;
31
32 close c_tab;
33
34 end;
35 /
PL/SQL procedure successfully completed.
SQL>
bulk collect ... forall it doesn't do the insert row by row. There is only one DML operation, one of the reasons for the hefty increase in performance. It might be possible to create a rowtype of a ref_cursor - I've never tried, in which case bulk collect will still work but it seems like you're going down a very complicated route. Does it really have to be dynamic?