1.文件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
2.建表
CREATE TABLE lob_tab (
number_content    NUMBER(10),
varchar2_content VARCHAR2(100),
date_content      DATE,
clob_content      CLOB,
blob_content      BLOB
);
3.控制文件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)

4.运行
sqlldr userid=test/test@db10g control=lob_test.ctl log=lob_test.log bad=lob_test.bad

5.查询
SELECT number_content,
       varchar2_content,
       TO_CHAR(date_content, 'DD-MON-YYYY') AS date_content,
       clob_content,
       DBMS_LOB.getlength(blob_content) AS blob_length
FROM   lob_tab;

ITPub博客

Loading LOB Data Using SQLLoader

最新文章