title: How to use forall statement in bulk data insert of csv file in APEX application
body: <p>I am using below code to upload the data from csv file to db table using APEX 5 but performance is very slow as .csv file is very large. How can I use forall statement in below code to run the query faster? I tried to replace execute immediate statement with forall but its not working. Can someone guide me on this?</p>
  
  <pre><code>    DECLARE
     v_blob_data         BLOB;
     v_blob_len          NUMBER;
     v_position          NUMBER;
     v_raw_chunk         RAW (10000);
     v_char              CHAR (1);
     c_chunk_len         NUMBER := 1;
     v_line              VARCHAR2 (32767) := NULL;
     v_data_array        wwv_flow_global.vc_arr2;
     v_rows              NUMBER;
     v_sr_no             NUMBER := 1;
     v_filename          VARCHAR2 (2000)
                            := APEX_UTIL.get_session_state ('P4_FILE_BROWSE');
     v_idx               NUMBER;
     v_first_line_done   BOOLEAN := FALSE;
     v_error_cd          NUMBER := 0;
  
     TYPE my_rec IS RECORD
     (
        company_code   XXX_COMP_TABLE.company_code%TYPE,
        cost_center    XXX_COMP_TABLE.cost_center%TYPE,
        user_name      XXX_COMP_TABLE.user_name%TYPE,
        start_date     XXX_COMP_TABLE.effective_start_date%TYPE,
        end_date       XXX_COMP_TABLE.effective_end_Date%TYPE
     );
  
     TYPE v_coll_rec IS TABLE OF my_rec     INDEX BY PLS_INTEGER;
  
     v_col1 v_coll_rec;
  
  BEGIN
     SELECT blob_content
       INTO v_blob_data
       FROM apex_application_temp_files
      WHERE name = v_filename;
  
     v_blob_len := DBMS_LOB.getlength (v_blob_data);
     v_position := 1;
  
     --wwv_flow.debug('v_position'||v_position);
     LOOP
        v_idx := DBMS_LOB.INSTR (v_blob_data, '0A', v_position);
  
        -- wwv_flow.debug('v_idx'||v_idx);
        IF v_idx = 0
        THEN
           wwv_flow.debug ('in if');
           v_line :=
              UTL_RAW.cast_to_varchar2 (
                 DBMS_LOB.SUBSTR (v_blob_data, 32767, v_position));
        ELSE
           -- wwv_flow.debug('in else');
           v_line :=
              UTL_RAW.cast_to_varchar2 (
                 DBMS_LOB.SUBSTR (v_blob_data, v_idx - v_position, v_position));
        END IF;
  
        wwv_flow.debug ('v_line' || v_line);
        EXIT WHEN v_idx = 0;
        v_position := v_idx + 1;
        v_line := REPLACE (v_line, ',', ':');
        v_data_array := wwv_flow_utilities.string_to_table (v_line);
  
        IF (v_first_line_done != TRUE)
        THEN
           v_first_line_done := TRUE;
  
           --wwv_flow.debug('v_data_array(5) '||v_data_array(5) );
           IF     v_data_array (1) = 'COMPANY_CODE'
              AND v_data_array (2) = 'COST_CENTER_CODE'
              AND v_data_array (3) = 'USER_NAME'
              AND v_data_array (4) = 'EFFECTIVE_START_DATE' --AND v_data_array(5) = 'EFFECTIVE_END_DATE'
           THEN
              v_error_cd := 0;
           ELSE
              v_error_cd := 1;
           END IF;
        ELSIF (v_first_line_done = TRUE AND v_error_cd = 0)
        THEN
           FORALL indx IN 1 .. v_col1.COUNT
              INSERT
                into XXX_COMP_TABLE
              VALUES v_col1 (indx);
        END IF;
  
        v_line := NULL;
        v_sr_no := v_sr_no + 1;
     END LOOP;
  END;
  </code></pre>