Oracle SQL-Loader以值有效地处理内部双引号

Question

我有一些Oracle SQL Loader的挑战，并寻找一个高效和简单的解决方案。我要加载的源文件是管道|分隔的，其中的值用双引号"括起来。问题似乎是有些值包含内部双引号。

例如：..."|"a":"b"|"...

这使我的记录被拒绝，理由是：

no terminator found after TERMINATED and ENCLOSED field

网络上有各种各样的解决方案，但似乎不适合：

1在引用引号时，我试图替换所有内部双引号，但是当对控制文件中的太多字段(我有~2000+字段并使用填充只加载子集)应用此函数时，加载程序再次抱怨：

SQL*Loader-350: Syntax error at line 7.
Expecting "," or ")", found ",".
field1  char(36) "replace(:field1,'"','""')",

(我不知道为什么，但当将此解决方案应用于一小部分列时，它似乎确实有效)

问题是，所有字段都可能包含内部双引号。

2当省略全局optionally enclosed by '"'时，我能够加载所有数据，但是所有封闭的引号都会成为目标表中数据的一部分。

3我可以省略全局optionally enclosed by '"'语句，只将它放在选定的字段中，而在其余字段上尝试"replace(:field1,'"','""')"语句，但这很难实现，因为我无法预先知道哪些可疑字段包含内部双引号。

以下是我的问题：

1. 没有简单的方法说服加载程序小心处理内部双引号(当值被它们括起来时)吗？
2. 如果我被迫修复数据ad-hock，是否有一个Linux命令只将内部双引号转换为另一个字符串/char，例如单引号？
3. 如果我被迫将带有引号的数据加载到目标表，那么是否有一种简单的方法可以同时从所有字段中删除括起来的双引号(该表有大约1000列)。对于非常大的表，解决方案对性能实用吗？

Answer 1

如果您在封闭的字段中没有管道，则可以从控制文件中执行。如果您可以在字段中同时使用管道和双引号，那么我认为您别无选择，只能对文件进行预处理，不幸的是。

您的解决方案1 (取代双引号使用SQL运算符 )发生得太晚了，无法使用；在执行SQL步骤之前，分隔符和分隔符已经被SQL*Loader解释了。您的解决方案2 (忽略附件)将与1一起工作，直到其中一个字段包含管道字符为止。解决方案3与全局使用1和/或2存在相同的问题。

指定分隔符的文档提到：

有时，数据中还必须包括作为分隔符的标点符号。为了实现这一点，两个相邻的分隔符字符被解释为字符的单个出现，该字符包含在数据中。

换句话说，如果您在字段中重复双引号，那么它们将被转义并显示在表数据中。由于无法控制数据生成，因此可以对所获得的文件进行预处理，以便用转义双引号替换所有双引号。除了你不想把它们全部替换--那些实际上是真正的封闭物的那些不应该被转义。

您可以使用正则表达式来针对相关字符跳过其他字符。不是我的强项，但我认为你可以用前瞻性和回顾性断言来完成这个任务。

如果您有一个名为orig.txt的文件，其中包含：

"1"|A|"B"|"C|D"
"2"|A|"B"|"C"D"
3|A|""B""|"C|D"
4|A|"B"|"C"D|E"F"G|H""

你可以这样做：

perl -pe 's/(?<!^)(?<!\|)"(?!\|)(?!$)/""/g' orig.txt > new.txt

它寻找一个双引号，它的前面没有行开始锚点或管道字符；并且后面没有管道字符或线尾锚；并且只替换那些转义(加倍)双引号。这将使new.txt包含：

"1"|A|"B"|"C|D"
"2"|A|"B"|"C""D"
3|A|"""B"""|"C|D"
4|A|"B"|"C""D|E""F""G|H"""

字段开头和结尾处的双引号没有修改，但中间的双引号现在被转义了。如果然后用带有双引号附件的控制文件加载该文件：

load data
truncate
into table t42
fields terminated by '|' optionally enclosed by '"'
(
  col1,
  col2,
  col3,
  col4
)

然后你就会有：

select * from t42 order by col1;

      COL1 COL2       COL3       COL4                
---------- ---------- ---------- --------------------
         1 A          B          C|D                 
         2 A          B          C"D                 
         3 A          "B"        C|D                 
         3 A          B          C"D|E"F"G|H"        

希望能和你的原始数据吻合。可能有一些边缘情况不起作用(就像双引号后面跟着字段中的管道)，但是要想解释别人的数据，你所能做的是有限度的。当然，也可能有(很多)更好的正则表达式模式。

您还可以考虑使用外部表而不是SQL*Loader，如果数据文件是(或者可以)在Oracle目录中，并且您有正确的权限。您仍然需要修改该文件，但是可以使用preprocessor指令自动完成，而不是在调用SQL*Loader之前显式地执行该操作。