oracle 如何使用 SQL*Loader 加载固定宽度的数据文件，该文件没有行分隔符但具有带换行符的字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9166792/
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 you load a fixed width data file using SQL*Loader which has no Line Delimiter but has fields with newline characters?
提问by ScrappyDev
I need to load a flat file into an oracle database using SQL*Loader.
The flat file contains a field that contains newline characters as valid data.
The file is NOT line delimited by a newline character.
我需要使用 SQL*Loader 将平面文件加载到 oracle 数据库中。
平面文件包含一个包含换行符作为有效数据的字段。
该文件不是由换行符分隔的行。
How would I Modify the following control file to do this?
我将如何修改以下控制文件来做到这一点？
LOAD DATA
INFILE 'mydata.dat'
INTO TABLE emp
( field1 POSITION(1:4) INTEGER EXTERNAL,
field2 POSITION(6:15) CHAR,
big_field POSITION(17:7000) CHAR
)
Note: I have no control over the format of the incoming file.
注意：我无法控制传入文件的格式。
Note: ... indicates that the data continues to the end of the field
example:
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
...
result:
field1: 1234
field2: 67890abcde
big_field: ghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
1234567890abcdefghijklmnopqrstuvwxyz
...
采纳答案by AnBisw
The problem here is that each line will be interpreted as a record in the flat file rather than a field. I am guessing that you may have to re format the file with a delimiter such as a comma or a tab or a pipe '|' with the '\n' so that the Loader interprets it as a one single record. Any occurrence of a new line '\n' will be treated as a new record I presume. refer to the link below-
这里的问题是每一行都将被解释为平面文件中的记录而不是字段。我猜您可能必须使用分隔符（例如逗号或制表符或管道“|”）重新格式化文件 使用 '\n' 以便加载程序将其解释为单个记录。任何出现的新行 '\n' 都将被视为我认为的新记录。请参考以下链接——
http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_concepts.htm#sthref718
http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_concepts.htm#sthref718
You cannot the format of the incoming file, but you can read that file and create a proper formatted mydata.dat for the SQL Loader. The whole point is to make the Loader understand 'what is your record terminator?', other wise your file would be like one huge record.
您无法确定传入文件的格式，但可以读取该文件并为 SQL 加载程序创建格式正确的 mydata.dat。重点是让加载程序了解“您的记录终止符是什么？”，否则您的文件将像一个巨大的记录。
回答by Tony Hopkinson
Never tried it myself but apparently, you need to find out what they used as a row delimiter. and pass in that char as hex. This one picks up 5E which is #
我自己从未尝试过，但显然，您需要找出它们用作行分隔符的内容。并将该字符作为十六进制传递。这一个拿起 5E 这是 #
load data infile test.dat "str X'5E'"
加载数据 infile test.dat "str X'5E'"
No idea what versions it's applicable to though, might be worth hitting the F1 key and looking for row delimiter..
不知道它适用于哪些版本，可能值得按 F1 键并寻找行分隔符..
???? There's no row delimiter. Oh have to write something to but one in then, if you want to use something like SQL*Loader.
???? 没有行分隔符。哦，如果你想使用 SQL*Loader 之类的东西，就必须写一些东西，但只有一个。
