Oracle数据库使用SQLLDR加载ELF文件时回车换行符处理问题咨询

问题背景 #

我们收到的ELF文件有时会在段/字段末尾携带回车符或换行符，由于绝大多数临时表（stage table）列的数据类型为VARCHAR2，因此数据可正常加载至临时表。
但将数据从临时表迁移至事务表时，若临时表中存储的数值型、小数字段包含回车或换行符，就会抛出数据转换错误。正常情况下文件中每个段应以分隔符|结尾，若段末尾未添加该分隔符，就会在临时表向事务表同步数据时触发上述问题。

在上述数据截图中，第1行数据可正常加载，但第2、3行段的最后一个字段值插入表时会携带回车符。

当前使用的表加载控制文件定义如下：

OPTIONS (ROWS=1000, READSIZE=2097152, BINDSIZE=2097152, PARALLEL=TRUE)
LOAD DATA
INFILE 'ELFFilePath'
APPEND

INTO TABLE STAGE_TABLE1
WHEN
FEED='FEED'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
ELEMENT_NUMBER POSITION(1) DECIMAL EXTERNAL,
COLUMN1 CHAR(5),
COLUMN2 CHAR(11),
COLUMN3 CHAR(10),
)

INTO TABLE STAGE_TABLE2
WHEN
DOMAIN='DOMAINNAME'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
ELEMENT_NUMBER POSITION(1) DECIMAL EXTERNAL,
COLUMN1 CHAR(5),
COLUMN2 CHAR(11),
COLUMN3 DECIMAL EXTERNAL,
COLUMN4 NUMBER
)

INTO TABLE STAGE_TABLE3
WHEN
DOMAIN='DOMAINNAME2'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
ELEMENT_NUMBER POSITION(1) DECIMAL EXTERNAL,
COLUMN1 CHAR(5),
COLUMN2 CHAR(11),
COLUMN3 CHAR(30),
COLUMN4 CHAR(30),
COLUMN5 CHAR(50),
COLUMN6 CHAR(15)
)

目标：通过SQLLDR加载数据时对回车/换行符的正确处理，避免后续数据同步报错。

可行落地方案（按优先级排序） #

方案1：控制文件内直接加字段清洗逻辑（优先推荐，无额外依赖） #

无需修改源文件、无需调整表结构，直接在控制文件的字段定义后绑定TRIM函数，加载时自动剔除字段首尾的空格、回车、换行类控制字符，从源头避免脏数据写入临时表。
修改后的控制文件核心配置如下，注意修正原配置中字段末尾多余的逗号（会触发SQLLDR语法错误）：

OPTIONS (ROWS=1000, READSIZE=2097152, BINDSIZE=2097152, PARALLEL=TRUE)
LOAD DATA
INFILE 'ELFFilePath'
APPEND

INTO TABLE STAGE_TABLE1
WHEN FEED='FEED'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
  ELEMENT_NUMBER POSITION(1) DECIMAL EXTERNAL "TRIM(:ELEMENT_NUMBER)",
  COLUMN1 CHAR(5) "TRIM(:COLUMN1)",
  COLUMN2 CHAR(11) "TRIM(:COLUMN2)",
  COLUMN3 CHAR(10) "TRIM(:COLUMN3)"
)

INTO TABLE STAGE_TABLE2
WHEN DOMAIN='DOMAINNAME'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
  ELEMENT_NUMBER POSITION(1) DECIMAL EXTERNAL "TRIM(:ELEMENT_NUMBER)",
  COLUMN1 CHAR(5) "TRIM(:COLUMN1)",
  COLUMN2 CHAR(11) "TRIM(:COLUMN2)",
  COLUMN3 DECIMAL EXTERNAL "TRIM(:COLUMN3)",
  COLUMN4 NUMBER "TRIM(:COLUMN4)"
)

INTO TABLE STAGE_TABLE3
WHEN DOMAIN='DOMAINNAME2'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
  ELEMENT_NUMBER POSITION(1) DECIMAL EXTERNAL "TRIM(:ELEMENT_NUMBER)",
  COLUMN1 CHAR(5) "TRIM(:COLUMN1)",
  COLUMN2 CHAR(11) "TRIM(:COLUMN2)",
  COLUMN3 CHAR(30) "TRIM(:COLUMN3)",
  COLUMN4 CHAR(30) "TRIM(:COLUMN4)",
  COLUMN5 CHAR(50) "TRIM(:COLUMN5)",
  COLUMN6 CHAR(15) "TRIM(:COLUMN6)"
)

Oracle内置的TRIM函数默认会清除字符串首尾所有空格、chr(10)（换行符）、chr(13)（回车符）类不可见控制字符，清洗逻辑在数据写入表前直接完成，不会增加后续同步环节的故障风险。

方案2：显式指定SQLLDR行终止符 #

如果源文件换行格式固定（比如全为Windows格式\r\n或Unix格式\n），可以在INFILE参数后显式声明行终止字符串，让SQLLDR解析行时自动丢弃尾部控制字符，配置示例：

-- Windows格式文件用这个
INFILE 'ELFFilePath' "STR '\r\n'"
-- Unix/Linux格式文件用这个
-- INFILE 'ELFFilePath' "STR '\n'"

该方案的局限是如果源文件混存不同格式的换行符，会出现行解析错位，稳定性弱于方案1。

方案3：加载前预处理源文件 #

如果加载流程允许增加前置节点，可以在调用SQLLDR前先批量清理文件尾部的控制字符，Linux环境直接用sed命令处理即可：

# 批量剔除文件所有行尾的回车符
sed -i 's/\r$//' /path/to/your/elf_file

Windows环境可通过PowerShell的替换命令完成同类清理，该方案适合文件体量小、源格式固定的场景。

避坑提示 #

不要为了兼容这类脏数据将事务表的数值类字段修改为VARCHAR2类型，会引发后续关联计算、数据校验的更多问题，在数据入临时表的环节完成清洗是成本最低的处理方式。

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