I am trying to insert data from a collection into the table using FORALL
, I expected the process to be fast but unfortunately process is rather slow and didn't show any major performance improvement.
Below code:
create or replace Procedure data_test (E in nchar, J in number)
AS
Exec_data JR_Table; //Created a nested table variable
BEGIN
select JR_Type (job_id, e_id, USER_NAME, start_time, end_time)
bulk collect into Exec_data //Bulk collect into this variable
from ( taBLE);
FORALL counter in Exec_data.first..Exec_data.last //insert using FORALL
insert into DATA_TEMP1 Values
(Exec_data(counter).JOB_ID, Exec_data(counter).E_ID, Exec_data(counter).START_TIME, Exec_data(counter).END_TIME;
commit;
End;
Above doesn't produce any satisfactory results with just 1300 records to insert in table and time taken is 6min, Instead I want to try using FORALL in below way:
FORALL counter in Exec_data.first..Exec_data.last //insert using FORALL
insert into DATA_TEMP1 Values
(Exec_data(counter));
Expecting above code change will speed up the process.
When tried to compile the code getting error:
Error(474,33): PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
I am sure their should be a way to get this work. I am not a core Oracle developer, I was able to write the above code by studying in few sites.