如何在Apex应用程序中的CSV文件的批量数据插入中使用forall语句

Question

我使用以下代码将数据从CSV文件上传到使用Apex 5的DB表，但性能非常慢，因为.CSV文件非常大。如何在下面代码中使用forall语句来更快地运行查询？我试图用forall替换执行即时陈述，但它不起作用。有人可以指导我吗？

    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;

Answer 1

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; 
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_coll_rec.COUNT 
INSERT INTO 
( SELECT company_code, 
cost_center, 
user_name , 
start_date, 
end_date 
FROM XXX_COMP_TABLE
) 
VALUES v_coll_rec (indx) ;          
    END IF; 
  v_line := NULL; v_sr_no := v_sr_no + 1;  
END LOOP;
END;