indexing - insert whole table types in Oracle into database without index -

i have new question must solved! had created table type

type ebrbktable table of ebtdccrbk%rowtype index pls_integer; rbktable        ebrbktable; 

and insert data table following statements

rbktable(inserttable).bdadduserid             := 'ft_rbk_tdcc'; 

the last insert following statement

forall in 1..inserttable - 1 insert ebtdccrbk values rbatable(i); 

i ask whether there ways insert type once without count(i)

thanks!

a forall statement not loop such.

what send elements of array sql engine in 1 go, enables sql insert rows without having go pl/sql engine more data.

in other words forall removes context switching have in regular loop.

we can show simple trace. consider:

sql> alter session set sql_trace=true;  session altered.  sql> declare   2    type ebrbktable table of ebtdccrbk%rowtype index pls_integer;   3    rbktable        ebrbktable;   4  begin   5   6    idx in 1..100000   7    loop   8      rbktable(idx).bdadduserid := dbms_random.string('a', 10);   9    end loop;  10  11    forall idx in 1..rbktable.count  12    insert ebtdccrbk values rbktable(idx);  13    commit;  14  15  end;  16  / 

in sql trace see:

insert ebtdccrbk values  (:b1 )   call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- parse        1      0.00       0.00          0          0          0           0 execute      1      0.07       0.07          1        724       3066      100000 fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2      0.07       0.07          1        724       3066      100000 

vs regluar loop:

sql> alter session set sql_trace=true;  session altered.  sql> declare   2    type ebrbktable table of ebtdccrbk%rowtype index pls_integer;   3    rbktable        ebrbktable;   4  begin   5   6    idx in 1..100000   7    loop   8      rbktable(idx).bdadduserid := dbms_random.string('a', 10);   9    end loop;  10  11    idx in 1..rbktable.count  12    loop  13      insert ebtdccrbk values rbktable(idx);  14    end loop;  15    commit;  16  17  end;  18  /  pl/sql procedure completed.  sql> alter session set sql_trace=false; 

and trace file shows:

insert ebtdccrbk values  (:b1 )   call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- parse        1      0.00       0.00          0          0          0           0 execute 100000      3.33       3.24          1        689     104216      100000 fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total   100001      3.33       3.24          1        689     104216      100000 

notice trace file in forall version had 1 execute, i.e. sql engine did work in 1 hit. in for loop variant, sql engine had 100k executes, , used lot more cpu same job (as had lot of single operations , session context switching pl/sql->sql each inserted row.