Oracle SQL Loader如何替换数据中嵌入的CR/LF并添加分隔符？

问题分析 #

你当前的CONTINUEIF THIS PRESERVE配置虽能拼接被双引号包裹的跨行字段，但会移除字段内的换行符，导致后续replace(:text_column,'\n','/')无法生效。核心原因是CONTINUEIF在拼接时已丢弃嵌入的换行，需调整加载策略保留换行后再做替换。

解决方案 #

方法1：移除CONTINUEIF，依赖OPTIONALLY ENCLOSED BY处理跨行字段 #

SQL Loader的OPTIONALLY ENCLOSED BY本身支持识别被引号包裹的字段内的换行（需明确行终止符），无需额外CONTINUEIF配置。修改后的.ctl文件如下：

LOAD DATA
INFILE 'incoming_data.txt' "str '\r\n'"  -- 明确指定行终止符为CR/LF
INTO TABLE my_table
TRUNCATE
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
text_column                     char(150) "replace(replace(:text_column, chr(13), ''), chr(10), '/')",
date_column                     date 'YYYY-MM-DD HH24:MI:SS',
number_column                   integer external,
another_text_column             char(150) terminated by whitespace
)

说明：

• INFILE ... "str '\r\n'"：明确告知SQL Loader行终止符为CR/LF，使其忽略双引号包裹字段内的CR/LF，将其作为字段内容保留。
• replace(replace(:text_column, chr(13), ''), chr(10), '/')：先移除Windows换行中的CR（chr(13)），再将LF（chr(10)）替换为斜杠；若为Linux/Unix环境，可简化为replace(:text_column, chr(10), '/')。

方法2：调整CONTINUEIF配置，保留换行符 #

若需保留CONTINUEIF，可改用CONTINUEIF NEXT确保拼接时保留换行，示例如下：

LOAD DATA
INFILE 'incoming_data.txt'
CONTINUEIF NEXT (1:1) = '"'  -- 下一行开头为双引号时，续接当前行并保留换行
INTO TABLE my_table
TRUNCATE
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
text_column                     char(150) "replace(replace(:text_column, chr(13), ''), chr(10), '/')",
date_column                     date 'YYYY-MM-DD HH24:MI:SS',
number_column                   integer external,
another_text_column             char(150) terminated by whitespace
)

说明：

• CONTINUEIF NEXT会检查下一行首字符，若为双引号则续接当前行，且保留两行间的换行符，让后续replace函数可正常识别替换。

测试建议 #

先拿少量数据测试，确认text_column中的换行已被正确替换为斜杠；根据操作系统（Windows/Linux/Unix）调整换行符的处理逻辑。

内容的提问来源于stack exchange，提问作者Don Del Grande