ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 通过SQLLDR导入LOB数据

通过SQLLDR导入LOB数据

原创 Linux操作系统 作者:jolly10 时间:2009-03-09 14:29:33 0 删除 编辑
本例通过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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/271283/viewspace-564926/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-20

  • 博文量
    263
  • 访问量
    769159