使用apex_data_parser导入大Excel文件报错的解决方案咨询

问题描述 #

使用apex_data_parser导入小Excel文件正常，但上传43MB大文件时耗时极长，最终抛出无意义错误（如“上传文件中无数据”）。当前采用逐行插入以定位错误行，核心插入代码如下：

Insert into E5ASSET
 (CODE,NAME,PERSIANTITLE,UNITPRICE,ISACTIVE,ISPRODUCTION,ISTOOLS,T5TOOLS_ID,SERIALNUMBER,COP,EQPTYPE,E5PARENT_ID,R5DEPT_ID,E5CLASS_ID,E5TYPE_ID,E5CATEGORY_ID,E5PROFILE_ID,
 E5HC_CRITICALITY_ID,E5HC_SHUTDOWNTYPE_ID,M5MFC_ID,E5EQUIPMENTSTATUS_ID,EQUIPMENTVALUE,MODEL,REVISION,E5HC_CURRENCY_ID,E5HC_UOM_ID,COMMISSIONDATE)
 
 Select col003 CODE,col004 NAME,col005 PERSIANTITLE,col006 UNITPRICE,col007 ISACTIVE,col008 ISPRODUCTION,col009 ISTOOLS,q1.id T5TOOLS_ID,col012 SERIALNUMBER,col013 COP,
 col014 EQPTYPE,q2.id E5PARENT_ID,q3.id R5DEPT_ID,q4.id E5CLASS_ID,q5.id E5TYPE_ID,q6.id E5CATEGORY_ID,q7.id E5PROFILE_ID,q8.id E5HC_CRITICALITY_ID,q9.id E5HC_SHUTDOWNTYPE_ID,
 q10.id M5MFC_ID,q11.id E5EQUIPMENTSTATUS_ID,col035 EQUIPMENTVALUE,col036 MODEL,col037 REVISION,q12.id E5HC_CURRENCY_ID,q13.id E5HC_UOM_ID,col042 COMMISSIONDATE 
 
 from apex_application_temp_files f,
                      table ( apex_data_parser.parse (  
                             p_content=> f.blob_content,
                             p_skip_rows=>207833,
                             p_max_rows=>207834,
                             p_file_name=> f.filename)) p
                             left join T5TOOLS q1 on  q1.code=p.col011 and  q1.r5Organization_id=12377745557146463808
                             left join E5EQUIPMENT q2 on  nvl(q2.code,'*')=p.col016 and  q2.r5Organization_id=12377745557146463808
                             left join R5DEPT q3 on  q3.code=p.col018 and  q3.r5Organization_id=12377745557146463808
                             left join E5CLASS q4 on  q4.code=p.col020 and  q4.r5Organization_id=12377745557146463808
                             left join E5TYPE q5 on  q5.code=p.col022 and  q5.r5Organization_id=12377745557146463808
                             left join E5CATEGORY q6 on  q6.code=p.col024 and  q6.r5Organization_id=12377745557146463808
                             left join E5PROFILE q7 on  q7.code=p.col026 and  q7.r5Organization_id=12377745557146463808 
                             left join E5HC q8 on  q8.code=p.col028 and  q8.hc_id=6 and  q8.r5Organization_id=12377745557146463808
                             left join E5HC q9 on  q9.code=p.col030 and  q9.hc_id=7 and  q9.r5Organization_id=12377745557146463808
                             left join M5MFC q10 on  q10.code=p.col032 and  q10.r5Organization_id=12377745557146463808
                             left join E5EQUIPMENTSTATUS q11 on  q11.code=p.col034 and  q11.r5Organization_id=12377745557146463808
                             left join E5HC q12 on  q12.code=p.col039 and  q12.hc_id=1 and  q12.r5Organization_id=12377745557146463808
                             left join E5HC q13 on  q13.code=p.col041 and  q13.hc_id=7 and  q13.r5Organization_id=12377745557146463808

优化方案 #

1. 批量分段处理，降低单次解析压力 #

apex_data_parser单次解析大文件会占用大量内存和CPU，建议按固定行数分段处理：

• 先通过apex_data_parser.get_num_rows获取Excel总行数
• 循环调用apex_data_parser.parse，每次设置p_skip_rows和p_max_rows为当前段的起始和结束行（比如每次处理1000行）
• 分段插入数据，既减少单次解析的资源占用，也能通过循环变量快速定位错误行范围

2. 预加载关联表数据到临时表，减少关联查询开销 #

当前SQL包含13个左连接，每行数据都要重复查询关联表，大数量下性能急剧下降：

• 提前将所有关联表中r5Organization_id=12377745557146463808的数据导入临时表，示例：

  CREATE GLOBAL TEMPORARY TABLE temp_t5tools AS
  SELECT id, code FROM T5TOOLS WHERE r5Organization_id=12377745557146463808;
  CREATE INDEX idx_temp_t5tools_code ON temp_t5tools(code);
  

• 插入SQL中改为关联临时表，利用临时表的索引大幅降低关联查询耗时

3. 替换逐行插入为批量插入+错误日志表 #

逐行插入是核心性能瓶颈之一，改用批量插入配合错误日志表实现错误行定位：

• 创建错误日志表，记录错误行信息：

  CREATE TABLE E5ASSET_ERROR_LOG (
      row_num NUMBER,
      CODE VARCHAR2(255),
      NAME VARCHAR2(255),
      -- 其他对应E5ASSET的字段
      error_msg VARCHAR2(4000),
      created_date DATE DEFAULT SYSDATE
  );
  

• 使用INSERT ... LOG ERRORS INTO语法批量插入，自动捕获错误行：

  Insert into E5ASSET
  (CODE,NAME,...)
  Select col003,col004,...
  from ...
  LOG ERRORS INTO E5ASSET_ERROR_LOG ('BATCH_LOAD') REJECT LIMIT UNLIMITED;
  

• 插入完成后查询E5ASSET_ERROR_LOG即可获取错误行详情，性能比逐行插入提升数倍

4. 调整APEX环境参数 #

• 在APEX管理界面增加apex_data_parser的内存分配限制，避免解析大文件时内存不足
• 延长会话超时时间，防止大文件处理过程中因超时触发无意义错误

5. 优化Excel文件格式 #

• 将Excel保存为.csv格式，apex_data_parser解析CSV的性能远高于XLSX
• 清理Excel中的空行、冗余格式，减少文件体积和解析负担

内容的提问来源于stack exchange，提问作者user22817983