[转]通过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.txtThis is a clob test1[oracle@rhel131 ~]$ cat clob_test2.txtThis is a clob test2[oracle@rhel131 ~]$ cat blob_test1.docThis is a BLOB test1.[oracle@rhel131 ~]$ cat blob_test2.docThis is a BLOB test2.
需要导入的原始文件如下，可以看到LOB类型的位置直接写成文件名即可。
[oracle@rhel131 ~]$ cat lob_test_data.txt1,one,01-JAN-2006,clob_test1.txt,blob_test1.doc2,two,02-JAN-2006,clob_test2.txt,blob_test2.doc
控制文件如下，注意clob_filename和blob_filename的属性是FILLER，所以她们不会导入到表中，但是她们说明下面的栏位是LOB信息，这个是
导入LOB的重点。
[oracle@rhel131 ~]$ cat lob_test.ctlLOAD DATAINFILE 'lob_test_data.txt'INTO TABLE lob_tabFIELDS 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 A16SQL> COLUMN date_content FORMAT A12SQL> COLUMN clob_content FORMAT A20SQL> 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_length6 FROM lob_tab;
NUMBER_CONTENT VARCHAR2_CONTENT DATE_CONTENT CLOB_CONTENT BLOB_LENGTH-------------- ---------------- ------------ -------------------- -----------1 one 01-JAN-2006 This is a clob test1 222 two 02-JAN-2006 This is a clob test2 22