sqlldr 换行符数据跨多行

jnrjian

于 2023-05-06 17:37:14 发布

阅读量918

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/jnrjian/article/details/130532399

版权

APPLIES TO:

Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and later
Information in this document applies to any platform.

GOAL

Given this control file:

load data
infile '<FileName>' "str x'0D'"
into table <TableName>
replace
fields terminated by "|"
trailing nullcols
(<Column1>,
<Column2>,
<Column3> char(4000)
)

and these 2 rows of data (the first marked in green and the 2nd in blue):

23|654|this data is normal
1|22|radom
data spead between
lines

(Notice: The 2nd row spans multiple lines.)

SQL*Loader (sqlldr) loads the first row only and includes the ID of the second row at the end of it.

How can this data be successfully loaded?

SOLUTION

1. Add a delimiter at the end of each row (as opposed to line) of data. For instance, if the pipe "|" is used as a delimiter:

23|654|this data is normal|
1|22|radom
data spead between
lines|

2. Modify the str value in the control file to match the delimiter specified. For example, to get the appropriate value for the pipe "|" as a delimiter, enter:

SQL> select utl_raw.cast_to_raw( '|'||chr(10) ) from dual;

UTL_RAW.CAST_TO_RAW('|'||CHR(10))
-------------------------------------------------------------------------------
7C0A

Note: On Windows, you would use UTL_RAW.CAST_TO_RAW( '|'||chr(13)||chr(10) ).

So the control file would be:

load data
infile '<FileName>' "str X'7C0A'"
into table <TableName>
replace
fields terminated by "|"
trailing nullcols
(<Column1>,
<Column2>,
<Column3> char(4000)
)

Use of the STR Attribute:

This is perhaps the most flexible method of loading data with embedded newlines. Using the STR attribute, we can specify a new end-of-line character (or sequence of characters). This allows us to create an input data file that has some special character at the end of each line the newline is no longer special.

Use a sequence of characters, typically some special marker, and then a newline. This makes it easy to see the end-of-line character when viewing the input data in a text editor or some utility, as each record still has a newline at the end of it. The STR attribute is specified in hexadecimal as shown above.