title: Parallel executions of stored procedure using Nested table
body: <p>I am using nested tables in stored procedure and FORALL statements to speed up the procedure execution which is taking close to 30min for a run, When I execute the procedure from single system then execution is fast and I was able to get required output.</p>
  <p>Problem is when the same procedure is executed in parallel from multiple systems at same time, results are wrong. Assuming the behaviour being data inserted in same nested table at same time, but I am not sure how to achieve correct results or how to manipulate the nested table behaviour.</p>
  <p>Created a object first:</p>
  <pre><code>create or replace TYPE JR_Type AS OBJECT
  
  (
  &quot;JOB_ID&quot; NUMBER(38,0), 
      &quot;E_ID&quot; NCHAR(64), 
      &quot;USER_NAME&quot; NCHAR(32), 
      &quot;START_TIME&quot; TIMESTAMP (6), 
      &quot;END_TIME&quot; TIMESTAMP (6), 
  
  );
  </code></pre>
  <p>Create a nested table on this object.</p>
  <pre><code>create or replace TYPE JR_Table AS TABLE OF JR_Type ;
  </code></pre>
  <p>Now below way I used in procedure:</p>
  <p>My procedure is huge but in a simpler way I am using in below way.</p>
  <pre><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;
  </code></pre>
  <p>I am sure their should be a way to handle this but unfortunately it is out of my understanding, Hope I will get some help.</p>
  <p>Edit-------------------------------------------------------------------</p>
  <p>2 executions insert data in same table, FYI input data is different in 2 executions.</p>
  <pre><code>total records should be 120
  
  1st execution 74
  
  2nd execution 56
  </code></pre>
  <p>With this design records varies according to execution once I will get 80 or 90 0r 60.</p>