Nested table object usage in FORALL in oracle

424 Views Asked by At

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.

0

There are 0 best solutions below