Oracle 19c下使用SQL Loader加载CSV文件，如何生成包含错误详情的合并错误文件

嘿，作为SQL Loader新手，能想到要把错误记录和具体错误信息整合起来，这绝对是个提升排查效率的好想法！可惜SQL Loader本身并没有直接生成这种合并文件的内置功能，但我们有两种实用的方法可以实现你的需求，下面给你详细拆解：

方法一：解析SQL Loader输出文件（.log + .bad）生成合并文件 #

SQL Loader默认会生成两个关键文件：

• .bad：存储所有加载失败的原始CSV记录
• .log：记录加载统计、错误行号和对应的Oracle错误代码/描述

我们可以通过脚本（比如Shell、Python）来关联这两个文件的内容，步骤如下：

1. 确保生成完整的输出文件
   在你的SQL Loader命令里明确指定BADFILE和LOGFILE参数（其实默认也会生成，但显式指定更稳妥）：

   sqlldr username/password@database control=your_control_file.ctl bad=your_file.bad log=your_file.log
   

2. 解析.log文件提取错误映射
   打开.log文件，你会看到类似这样的错误条目：

   Record 5: Rejected - Error on table EMP, column HIREDATE.
   ORA-01843: not a valid month

   我们需要提取行号（比如5）和友好的错误描述（比如把ORA-01843转换成"Invalid date"）。这里可以写个简单的脚本：

   • 用Shell的话，结合grep和awk提取行号和错误信息，把它们存成一个键值对的临时文件（比如error_map.txt）
   • 用Python的话，读取.log文件，正则匹配行号和错误代码，再映射成友好描述

3. 关联.bad记录与错误信息
   逐行读取.bad文件（每一行对应.log里的一个错误行号），然后从错误映射文件中取出对应的错误描述，把两者拼接成新的合并文件（比如error_combined.csv），每条记录后面加上错误描述列。

举个简化的Shell脚本示例（仅供参考，你可能需要根据自己的日志格式调整）：

# 从.log提取行号和错误信息，生成映射文件
grep -A1 "Record.*Rejected" your_file.log | awk '
    /Record/ {split($2, arr, ":"); line_num=arr[1]}
    /ORA-/ {print line_num "," substr($0, index($0, "ORA-"))}' > error_map.txt

# 关联.bad和映射文件，生成合并文件
awk -F, 'NR==FNR {err[$1]=$2; next} {print $0 "," err[NR]}' error_map.txt your_file.bad > error_combined.csv

方法二：用Oracle外部表替代SQL Loader（更适合新手） #

如果你觉得写脚本麻烦，Oracle的外部表绝对是更友好的选择——它可以直接把CSV文件当作数据库表来查询，然后通过LOG ERRORS子句把错误记录和详细信息直接存入数据库的错误表，不需要处理外部文件。

步骤如下：

1. 创建目录对象（指向CSV所在文件夹）
   首先需要DBA权限创建目录，或者让DBA帮你创建：

   CREATE DIRECTORY csv_dir AS '/path/to/your/csv/folder';
   

2. 授予读写权限给你的用户

   GRANT READ, WRITE ON DIRECTORY csv_dir TO your_username;
   

3. 创建外部表
   定义CSV的格式（分隔符、列名、数据类型等），比如：

   CREATE TABLE emp_ext (
       empno NUMBER(4),
       ename VARCHAR2(10),
       hiredate DATE
   )
   ORGANIZATION EXTERNAL (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY csv_dir
       ACCESS PARAMETERS (
           RECORDS DELIMITED BY NEWLINE
           FIELDS TERMINATED BY ','
           MISSING FIELD VALUES ARE NULL
           (empno, ename, hiredate DATE "DD-MM-YYYY")
       )
       LOCATION ('your_csv_file.csv')
   )
   REJECT LIMIT UNLIMITED;
   

4. 加载数据并捕获错误
   用INSERT ... SELECT语句加载数据，同时用LOG ERRORS把错误记录存入错误表：

   -- 先创建错误表（如果不存在）
   BEGIN
       DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name => 'EMP');
   END;
   /
   
   -- 加载数据并记录错误
   INSERT INTO emp
   SELECT * FROM emp_ext
   LOG ERRORS INTO err$_emp ('load_20240520') REJECT LIMIT UNLIMITED;
   

5. 查询整合后的错误信息
   直接查询错误表err$_emp，里面包含：

   • ORA_ERR_MESG$：详细的错误描述（比如"ORA-01843: not a valid month"，你可以用CASE语句转换成"Invalid date"）
   • 原始的错误记录字段（EMPNO, ENAME, HIREDATE等）

   比如：

   SELECT 
       empno,
       ename,
       hiredate,
       CASE WHEN ORA_ERR_MESG$ LIKE '%ORA-01843%' THEN 'Invalid date'
            ELSE ORA_ERR_MESG$ END AS error_description
   FROM err$_emp;
   

新手友好提示 #

• 如果你刚接触SQL Loader，优先试试外部表的方法，它更直观，不需要处理复杂的文件解析脚本
• 不管用哪种方法，都要确保CSV的格式和目标表的列定义完全匹配（比如日期格式），这是最常见的加载错误原因

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