本例通过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