load multi-lines records using sqlldr

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/11/6
Subject: sqlldr导入多行记录
To: xiewenxiewen at gmail.com

三种方法

1.特殊符号表示一条记录的结束

比如：记录结束用分号+换行符表示

drop table multilines_1;

create table multilines_1 (
id int,
text varchar2(1000)
);

cat >multilines-1.dat <<'EOF'
1;Last week Motorola announced that it would be shifting gears
in its smartphone business to focus on Google's Android platform.;
2;In fact, the company's co-CEO, said it would be limiting its
efforts to Android, Windows Mobile, and its own operating system,
P2K.;
EOF

cat >multilines-1.ctl <<'EOF'
LOAD DATA
INFILE "multilines-1.dat" "str ';\n'"
TRUNCATE
INTO TABLE multilines_1
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
id INTEGER EXTERNAL,
text CHAR
)
EOF

sqlldr a/a control=multilines-1.ctl log=multilines-1.log

select * from multilines_1;

SQL> select * from multilines_1;

ID
----------
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
1
Last week Motorola announced that it would be shifting gears
in its smartphone business to focus on Google's Android platform.

2
In fact, the company's co-CEO, said it would be limiting its
efforts to Android, Windows Mobile, and its own operating system,
P2K.

导入的记录跟原文件中一样，也有换行

2.用CONTINUEIF参数合并多行

比如，根据每行最后一个字符判断记录是否结束

drop table multilines_2;

create table multilines_2 (
id int,
text varchar2(1000)
);

cat >multilines-2.dat <<'EOF'
1;Last week Motorola announced that it would be shifting gears
in its smartphone business to focus on Google's Android platform.;
2;In fact, the company's co-CEO, said it would be limiting its
efforts to Android, Windows Mobile, and its own operating system,
P2K.;
EOF

cat >multilines-2.ctl <<'EOF'
LOAD DATA
INFILE "multilines-2.dat"
TRUNCATE
CONTINUEIF LAST <> ';'
INTO TABLE multilines_2
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
id INTEGER EXTERNAL,
text CHAR
)
EOF

sqlldr a/a control=multilines-2.ctl data=multilines-2.dat log=multilines-2.log

select * from multilines_2;

SQL> select * from multilines_2;

ID
----------
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
1
Last week Motorola announced that it would be shifting gears in its smartphone business to focus on Google's Android platform.

2
In fact, the company's co-CEO, said it would be limiting its efforts to Android, Windows Mobile, and its own operating system, P2K.

多行合并成一行，换行没有了

3.用CONCATENATE合并

合并一定数量的多行，作为一行记录处理

drop table multilines_3;

create table multilines_3 (
id int,
text varchar2(1000)
);

cat >multilines-3.dat <<'EOF'
1;Last week Motorola announced that it would be shifting gears
in its smartphone business to focus on Google's
Android platform.
2;In fact, the company's co-CEO, said it would be limiting its
efforts to Android, Windows Mobile, and its own operating system,
P2K.
EOF

cat >multilines-3.ctl <<'EOF'
LOAD DATA
TRUNCATE
CONCATENATE 3
INTO TABLE multilines_3
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
id INTEGER EXTERNAL,
text CHAR
)
EOF

sqlldr a/a control=multilines-3.ctl data=multilines-3.dat log=multilines-3.log

select * from multilines_3;

SQL> select * from multilines_3;

ID
----------
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
1
Last week Motorola announced that it would be shifting gears in its smartphone business to focus on Google's Android platform.

2
In fact, the company's co-CEO, said it would be limiting its efforts to Android, Windows Mobile, and its own operating system, P2K.

也是多行变一行，换行没了

只能合并固定行数

4. 举例

导入数据文件

3;abadferrerer^M\
^M\
fasfrerererteter^M\
dfafaerrererer^M\
^M\
Fwerertterwt
4;feretrterteteqeeree^M\
^M\
foererntrptrtntrotrtnrtpro^M\
^M\
ojfreorrtrtrtr

(^M是\r回车符，ASCII码是13，可以按Ctrl-v,Ctrl-m输入)

想要第二个字段去掉^M\，得到如下结果

abadferrerer

fasfrerererteter

dfafaerrererer

Fwerertterwt

用CONTINUEIF+translate()实现

drop table multilines_4;
create table multilines_4 (
id int,
text varchar2(1000)
);

cat >multilines-4.dat <<'EOF'
3;abadferrerer^M\
^M\
fasfrerererteter^M\
dfafaerrererer^M\
^M\
Fwerertterwt
4;feretrterteteqeeree^M\
^M\
foererntrptrtntrotrtnrtpro^M\
^M\
ojfreorrtrtrtr
EOF
sed -i 's/\^M/\r/g' multilines-4.dat

cat >multilines-4.ctl <<'EOF'
LOAD DATA
TRUNCATE
CONTINUEIF LAST = '\\'
INTO TABLE multilines_4
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
id INTEGER EXTERNAL,
text CHAR "translate(:text,chr(13)||'\\',chr(10))"
)
EOF

sqlldr a/a control=multilines-4.ctl data=multilines-4.dat log=multilines-4.log

select * from multilines_4;

SQL> select * from multilines_4;

ID
----------
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
3
abadferrerer

fasfrerererteter
dfafaerrererer

Fwerertterwt

4
feretrterteteqeeree

foererntrptrtntrotrtnrtpro

ojfreorrtrtrtr