sqlldr大字段

建表的SQL：

CREATE TABLE lob_tab (
number_content NUMBER(10),
varchar2_content VARCHAR2(100),
date_content DATE,
clob_content CLOB,
blob_content BLOB
);

需要导入的LOB资料如下：

[oracle@rhel131 ~]$ cat clob_test1.txt
This is a clob test1
[oracle@rhel131 ~]$ cat clob_test2.txt
This is a clob test2
[oracle@rhel131 ~]$ cat blob_test1.doc
This is a BLOB test1.
[oracle@rhel131 ~]$ cat blob_test2.doc
This is a BLOB test2.

需要导入的原始文件如下，可以看到LOB类型的位置直接写成文件名即可。

[oracle@rhel131 ~]$ cat lob_test_data.txt
1,one,01-JAN-2006,clob_test1.txt,blob_test1.doc
2,two,02-JAN-2006,clob_test2.txt,blob_test2.doc

控制文件如下，注意clob_filename和blob_filename的属性是FILLER，所以她们不会导入到表中，但是她们说明下面的栏位是LOB信息，这个是

导入LOB的重点。

[oracle@rhel131 ~]$ cat lob_test.ctl
LOAD DATA
INFILE 'lob_test_data.txt'
INTO TABLE lob_tab
FIELDS TERMINATED BY ','
(number_content CHAR(10),
varchar2_content CHAR(100),
date_content DATE "DD-MON-YYYY" ":date_content",
clob_filename FILLER CHAR(100),
clob_content LOBFILE(clob_filename) TERMINATED BY EOF,
blob_filename FILLER CHAR(100),
blob_content LOBFILE(blob_filename) TERMINATED BY EOF)

用SQLLDR导入到表中：

[oracle@rhel131 ~]$ sqlldr scott/tiger control=lob_test.ctl log=lob_test.log bad=lob_test.bad

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Mar 2 11:23:32 2009

Commit point reached - logical record count 2

SQL> COLUMN varchar2_content FORMAT A16
SQL> COLUMN date_content FORMAT A12
SQL> COLUMN clob_content FORMAT A20
SQL> SELECT number_content,
2 varchar2_content,
3 TO_CHAR(date_content, 'DD-MON-YYYY') AS date_content,
4 clob_content,
5 DBMS_LOB.getlength(blob_content) AS blob_length
6 FROM lob_tab;

NUMBER_CONTENT VARCHAR2_CONTENT DATE_CONTENT CLOB_CONTENT BLOB_LENGTH
-------------- ---------------- ------------ -------------------- -----------
1 one 01-JAN-2006 This is a clob test1 22
2 two 02-JAN-2006 This is a clob test2 22