使用SQL Loader将CSV文件加载到Oracle 12c表时遇错误求助

场景概述 #

在Linux环境中使用SQL*Loader将CSV文件导入Oracle 12c的EM10SCHEMA.UNSUBSCRIBEENTRY_STAGE表时，遇到ORA-01858: a non-numeric character was found where a numeric was expected错误，错误指向CREATED列。

相关文件详情 #

控制文件（spam_complaints.ldr） #

load data
infile '/opt/Infor/Marketing/10.1.0.0/custom/OUTBOUND_SPAM_COMPLAINTS_20230_TEST1.CSV' \"str ';\r\n'\"
badfile 'spam.bad'
append
into table EM10SCHEMA.UNSUBSCRIBEENTRY_STAGE
fields terminated by \"\" OPTIONALLY ENCLOSED BY '\"\"'
TRAILING NULLCOLS
(EMAIL char(4000), created \"to_date(:CREATED,'ddmmyyyy hh24:mi:ss')\", UNSUBSCRIBED, ID, MODIFIED

示例CSV数据 #

EMAIL,CREATED,UNSUBSCRIBED,ID,MODIFIED
\"nari_90_09@hotmail.com\",\"2022-06-23 07:26:38\",\"Y\",\"263115\",\"2022-06-23 07:29:25\"

目标表DDL #

EMAIL         VARCHAR2(255 BYTE)              NOT NULL,
CREATED       DATE,
UNSUBSCRIBED  CHAR(1 BYTE)                    NOT NULL,
ID            NUMBER(19)                      NOT NULL,
MODIFIED      DATE                            NOT NULL

错误日志 #

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Mar 1 23:35:13 2023

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Control File:   /opt/Infor/Marketing/10.1.0.0/custom/spam_complaints.ldr
Data File:      /opt/Infor/Marketing/10.1.0.0/custom/OUTBOUND_SPAM_COMPLAINTS_20230_TEST1.CSV
  File processing option string: \"str ';^M\n'\"
  Bad File:     spam.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table EM10SCHEMA.UNSUBSCRIBEENTRY_STAGE, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMAIL                               FIRST  4000   ,       CHARACTER
    Enclosure string : '\"\"'
CREATED                              NEXT     *   ,       CHARACTER
    Enclosure string : '\"\"'
    SQL string for column : \"to_date(:CREATED,'ddmmyyyy hh24:mi:ss')\"
UNSUBSCRIBED                         NEXT     *   ,       CHARACTER
    Enclosure string : '\"\"'
ID                                   NEXT     *   ,       CHARACTER
    Enclosure string : '\"\"'
MODIFIED                             NEXT     *   ,       CHARACTER
    Enclosure string : '\"\"'

value used for ROWS parameter changed from 64 to 50
Record 1: Rejected - Error on table EM10SCHEMA.UNSUBSCRIBEENTRY_STAGE, column CREATED.
ORA-01858: a non-numeric character was found where a numeric was expected

问题根源 #

1. 日期格式不匹配：CSV中日期格式为YYYY-MM-DD HH24:MI:SS，但控制文件中to_date使用的格式是ddmmyyyy hh24:mi:ss，解析时因格式冲突报错。
2. 控制文件配置错误：
   • 字段分隔符未正确指定（CSV用逗号分隔，但配置为\"\"）；
   • 字段包围符语法错误（'\"\"'应为'"'）；
   • 行分隔符错误添加了分号，导致行解析异常；
   • 未跳过CSV表头行，表头被当作数据行解析。

修复后的控制文件 #

load data
infile '/opt/Infor/Marketing/10.1.0.0/custom/OUTBOUND_SPAM_COMPLAINTS_20230_TEST1.CSV'
badfile 'spam.bad'
append
into table EM10SCHEMA.UNSUBSCRIBEENTRY_STAGE
fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
skip 1 -- 跳过CSV表头行
(
    EMAIL char(4000),
    CREATED "to_date(:CREATED, 'YYYY-MM-DD HH24:MI:SS')",
    UNSUBSCRIBED,
    ID "to_number(:ID)", -- 显式转换为数字类型
    MODIFIED "to_date(:MODIFIED, 'YYYY-MM-DD HH24:MI:SS')"
)

关键修改说明 #

• 日期格式修正：将to_date的格式字符串改为YYYY-MM-DD HH24:MI:SS，匹配CSV中的日期格式；
• 字段分隔符修正：指定逗号为字段分隔符，符合CSV标准；
• 包围符修正：正确设置双引号为字段包围符，避免语法错误；
• 跳过表头：添加skip 1指令，忽略CSV第一行的表头数据；
• ID字段转换：显式添加to_number(:ID)，确保字符串类型的ID转换为表中的NUMBER类型；
• 行分隔符调整：移除错误的行分隔符配置，SQL*Loader默认识别Linux下的\n作为行分隔符。

额外验证建议 #

• 若CSV是Windows格式换行符（\r\n），可在infile后添加"str '\r\n'"配置；
• 确认MODIFIED列同样完成日期格式转换，避免同类错误；
• 先加载少量数据验证配置正确性，再执行批量导入。

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