如何在Oracle SQL Developer控制文件中处理双引号以加载数据

问题重现 #

你的SQL*Loader控制文件如下：

OPTIONS (SKIP=0)
LOAD DATA
BADFILE "Load_bad.bad"
TRUNCATE
INTO TABLE LOAD_DATA
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
{
  "NAME" char
  "AGE" char
  "ID" char
  "REASON" char
  "INTEREST" char 
  "EXPERIENCE" char
}

待加载的数据行：

myname|12|11|"need to switch" to parent|"java" c|2|

问题原因 #

OPTIONALLY ENCLOSED BY '"'规则要求字段要么完全被双引号包裹（如"完整字段内容"），要么无任何双引号。但你的数据中是字段内部分内容带双引号（如"need to switch" to parent），SQL*Loader会将第一个双引号视为字段起始标记，试图匹配对应的结束双引号，后续的非引号内容会被判定为无效格式，导致加载失败。

解决方法 #

方法1：移除包裹规则+批量清除双引号 #

直接删除控制文件中的OPTIONALLY ENCLOSED BY '"'，同时在字段定义中用REPLACE函数自动清除所有双引号，修改后的控制文件：

OPTIONS (SKIP=0)
LOAD DATA
BADFILE "Load_bad.bad"
TRUNCATE
INTO TABLE LOAD_DATA
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
  "NAME" char,
  "AGE" char,
  "ID" char,
  "REASON" char "REPLACE(:REASON, '\"', '')",
  "INTEREST" char "REPLACE(:INTEREST, '\"', '')",
  "EXPERIENCE" char
)

此方案会严格按|拆分字段，再自动清理每个字段内的双引号，无需手动修改数据文件。

方法2：正则表达式处理复杂场景 #

如果需要更精细的双引号清理规则（比如只移除开头/结尾的孤立双引号），可以使用REGEXP_REPLACE函数：
例如只移除字段开头的双引号：

"REASON" char "REGEXP_REPLACE(:REASON, '^\"', '')"

移除所有双引号的正则写法：

"REASON" char "REGEXP_REPLACE(:REASON, '\"', '')"

方法3：临时字段中转处理（适用于复杂逻辑） #

如果需要对原始字段做多步处理，可以定义临时FILLER字段，再将处理后的值映射到目标字段：

OPTIONS (SKIP=0)
LOAD DATA
BADFILE "Load_bad.bad"
TRUNCATE
INTO TABLE LOAD_DATA
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
  "NAME" char,
  "AGE" char,
  "ID" char,
  reason_temp char,
  "REASON" char "REPLACE(:reason_temp, '\"', '')",
  interest_temp char,
  "INTEREST" char "REPLACE(:interest_temp, '\"', '')",
  "EXPERIENCE" char
)

总结 #

优先推荐方法1，它既能适配当前不规范的双引号格式，又能保持控制文件的简洁性，实现自动加载无需手动干预。

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