sql loader- 带回车、换行的文件用 cr/lf 加载到 oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27027325/
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
sql loader- files with carriage return, line feed load into oracle with cr/lf
提问by lvss
I have data extract from sql server where data in most columns have carriage return and line feeds. I need to load them into oracle with the carriage return and line feed; basically I have to mirror the data form sql server 2012 to oracle 11g.
我有从 sql server 中提取的数据，其中大多数列中的数据都有回车和换行。我需要使用回车和换行将它们加载到 oracle 中；基本上我必须将数据形式 sql server 2012 镜像到 oracle 11g。
below is the sample of my extract file
下面是我的提取文件的示例
[#BOR#][#EOC#]109[#EOC#]4[#EOC#]testdata_Duplicate[#EOC#]testdata_Duplicate from chat[#EOC#]this
[#BOR#][#EOC#]109[#EOC#]4[#EOC#]testdata_Duplicate[#EOC#]testdata_Duplicate 来自聊天[#EOC#]this
is
是
carriage return field[#EOC]test2[#EOR#]
Here [#EOC#] is column delimiter, [#EOR#] is row delimiter. [#BOR#] indicates the beginning of row. Initially my loads failed to due to blank lines in the flat file(data extract). Then I used [#BOR#] with continueIf preserve clause so that sqlldr will not treat blank lines(cr/lf) as physical row.
这里 [#EOC#] 是列分隔符，[#EOR#] 是行分隔符。[#BOR#] 表示行的开始。最初，由于平面文件（数据提取）中的空白行，我的加载失败。然后我将 [#BOR#] 与 continueIf 保留子句一起使用，以便 sqlldr 不会将空行（cr/lf）视为物理行。
with [#BOR#] as a filler column my load works fine but carriage return or line feed are not loaded into oracle tables.
使用 [#BOR#] 作为填充列，我的加载工作正常，但没有将回车或换行加载到 oracle 表中。
My ctl file is as below
我的ctl文件如下
load data
truncate
CONTINUEIF NEXT preserve (1:7) <> "[#BOR#]"
into table sch1.tbl1
fields terminated by '[#EOC#]'
trailing nullcols (
field filler,
a_id integer external,
h_id integer external,
title char(128),
descn char(4000),
risk char(4000),
comment char(4000) terminated by '[#EOR#]')
In oracle sch1.tbl1 table column risk has data as 'this is carriage return field' instead of 'this
在 oracle sch1.tbl1 表中，风险列的数据为“这是回车字段”而不是“这
is
是
carriage return field'
I tried to replace char(10) with string [#crlf#] and use replace function in ctl like as below
我尝试用字符串 [#crlf#] 替换 char(10) 并在 ctl 中使用替换函数，如下所示
load data
truncate
CONTINUEIF NEXT preserve (1:7) <> "[#BOR#]"
into table sch1.tbl1
fields terminated by '[#EOC#]'
trailing nullcols (
field filler,
a_id integer external,
h_id integer external,
title char(128),
descn char(4000),
risk char(4000) "replace(:risk,[#crlf#],chr(10))"
comment char(4000) terminated by '[#EOR#]')
the sql loader errors out stating SQL*Loader-309: No SQL string allowed as part of field specification; I believe because my columns are CLOB data type I am not able to use replace function.
sql 加载器错误说明 SQL*Loader-309：不允许 SQL 字符串作为字段规范的一部分；我相信因为我的列是 CLOB 数据类型，所以我无法使用替换功能。
Please help me to load data from sql server with cr/lnFeed into oracle tables using sqlloader. Thank you in advance.
请帮助我使用 sqlloader 从带有 cr/lnFeed 的 sql server 加载数据到 oracle 表中。先感谢您。
采纳答案by lvss
Here is the solution that works for me. Instead of replacing the carriage return/line feed(cr/lf) in the extracted flat file with [#crlf#] I retain the cr/lf in the extracted data file. And then I changed my ctl file to handle the cr/lf with INFILE Clause with file name and " str '\n' ". For Unix env we need \n where in for windows we can use either \n or \r\n.
这是对我有用的解决方案。我没有用 [#crlf#] 替换提取的平面文件中的回车/换行符（cr/lf），而是保留提取的数据文件中的 cr/lf。然后我更改了我的 ctl 文件以处理带有文件名和“ str '\n' ”的 INFILE 子句的 cr/lf。对于 Unix env，我们需要 \n 而对于 Windows，我们可以使用 \n 或 \r\n。
see below
见下文
load data INFILE 'filename.dat' "str '\n'"
truncate
CONTINUEIF NEXT preserve (1:7) <> "[#BOR#]"
into table sch1.tbl1
fields terminated by '[#EOC#]'
trailing nullcols (
field filler,
a_id integer external,
h_id integer external,
title char(128),
descn char(4000),
risk char(4000),
comment char(4000) terminated by '[#EOR#]')
I tested it and data loaded with cr\lf.. I need to do more detailed testing, as of now I have tested one table I have many more. Meanwhile if any one has better solution I would be more than happy to try it out.
我测试了它，数据加载了 cr\lf.. 我需要做更详细的测试，截至目前我已经测试了一张我还有更多的表。同时，如果有人有更好的解决方案，我会很乐意尝试一下。
