通過SQLLDR匯入LOB資料

本例通過SQLLDR來匯入CLOB和BLOB物件。

建表的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

Copyright (c) 1982, 2005, Oracle. All rights reserved.

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