oracle 如何转义 SQL Loader 数据文件中的封闭字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1448731/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I escape an enclosure character in a SQL Loader data file?
提问by Jason Baker
I have a SQL*Loader control file that has a line something like this:
我有一个 SQL*Loader 控制文件，它有这样一行：
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '#'
Normally, I'd use a quotation mark, but that seems to destroy emacs's python syntax highlighting if used inside a multi-line string. The problem is that we are loading an ADDRESS_LINE_2column where only 7,000 out of a million records are loading because they have lines like this:
通常，我会使用引号，但如果在多行字符串中使用，这似乎会破坏 emacs 的 python 语法突出显示。问题是我们正在加载一个ADDRESS_LINE_2列，其中一百万条记录中只有 7,000 条正在加载，因为它们有这样的行：
...(other columns),Apt #2,(other columns)...
...(其他列),Apt #2,(其他列)...
Which is of course causing errors. Is there any way to escape the enclosing character so this doesn't happen? Or do I just need to choose a better enclosing character?
这当然会导致错误。有没有办法逃避封闭的字符，所以这不会发生？还是我只需要选择一个更好的封闭字符？
I've looked through the documentation, but don't seem to have found an answer to this.
我已经浏览了文档，但似乎没有找到答案。
回答by Jason Baker
I found it...
我找到了...
If two delimiter characters are encountered next to each other, a single occurrence of the delimiter character is used in the data value. For example, 'DON''T' is stored as DON'T. However, if the field consists of just two delimiter characters, its value is null.
如果遇到两个相邻的分隔符，则在数据值中使用单个出现的分隔符。例如，'DON''T' 存储为 DON'T。但是，如果该字段仅由两个分隔符组成，则其值为空。
回答by beshtian
Unfortunately, SqlLoadercomputes both occurrences of the delimiter while checking for max length of the field. For instance, DON''Twill be rejected in a CHAR(5)field, with ORA-12899: value too large for column blah.blah2 (actual: 6, maximum: 5).
At least in my 11gR2. Haven't tried in other versions....
不幸的是，SqlLoader在检查字段的最大长度时计算分隔符的两次出现。例如，DON'T将在CHAR(5)字段中被拒绝，带有ORA-12899: value too large for column blah.blah2 (actual: 6, maximum: 5). 至少在我的11gR2 中。其他版本没试过....
