上文提到，使用SQL*Loader工具加载数据到LOB字段比较复杂，真的是那样么？这个小文儿演示一下加载数据到CLOB字段。
这里“上文”指：《【实验】【SQL*Loader】使用SQLLDR将Linux的用户文件passwd导入数据库》http://space.itpub.net/519536/viewspace-613574

1.创建一个非常简单的包含CLOB字段的表t_sqlldr_clob
sec@secooler> create table t_sqlldr_clob (product_name varchar2(30),description clob);

Table created.

2.准备控制文件如下
secooler@db10gr2 /home/oracle$ cat load_t_sqlldr_clob.ctl
LOAD DATA
INFILE *
INTO TABLE t_sqlldr_clob
REPLACE
FIELDS TERMINATED BY ','
( product_name   ,
description
)
BEGINDATA
Rubik,This column contains a lot of descriptions about the Rubik Cube.
Toy,This column contains a lot of descriptions about the Toy.

3.使用SQLLDR命令行工具导入CLOB数据
secooler@db10gr2 /home/oracle$ sqlldr sec/sec control=load_t_sqlldr_clob.ctl

SQL*Loader: Release 10.2.0.3.0 - Production on Mon Aug 31 19:55:23 2009

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

Commit point reached - logical record count 2

4.查看一下操作的日志文件
secooler@db10gr2 /home/oracle$ cat load_t_sqlldr_clob.log

SQL*Loader: Release 10.2.0.3.0 - Production on Mon Aug 31 19:55:23 2009

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

Control File:   load_t_sqlldr_clob.ctl
Data File:      load_t_sqlldr_clob.ctl
Bad File:     load_t_sqlldr_clob.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table T_SQLLDR_CLOB, loaded from every logical record.
Insert option in effect for this table: REPLACE

   Column Name     Position   Len Term Encl Datatype
----------------- ---------- ----- ---- ---- -----------
PRODUCT_NAME           FIRST     *   ,       CHARACTER
DESCRIPTION             NEXT     *   ,       CHARACTER

Table T_SQLLDR_CLOB:
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array:                  33024 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             2
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Mon Aug 31 19:55:23 2009
Run ended on Mon Aug 31 19:55:23 2009

Elapsed time was:     00:00:00.07
CPU time was:         00:00:00.02

5.到数据库中查看一下
secooler@db10gr2 /home/oracle$ sqlplus sec/sec

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Aug 31 19:56:59 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

sec@secooler> desc t_sqlldr_clob
Name              Null?    Type
----------------- -------- ------------
PRODUCT_NAME               VARCHAR2(30)
DESCRIPTION                CLOB

sec@secooler> select * from t_sqlldr_clob;

PRODUCT DESCRIPTION
------- ---------------------------------------------
Rubik   This column contains a lot of descriptions about the Rubik Cube.
Toy     This column contains a lot of descriptions about the Toy.

OK，导入成功，与普通字段的导入完全一样，就是这么普通和简单！

6.小结
既然导入的过程这么简单，那为什么还要提到LOB字段的导入比较复杂呢？
因为在真实的生产环境里，LOB字段中存放的信息远远没有我这个实验中提到的这么简单。
例如，LOB字段中的信息存在“换行符号”，例如本例中的DESCRIPTION字段每行的描述信息是由多行信息组成，或者生产环境中的XML类数据，需要处理换行符号如何入库的问题。
如此种种的问题都会增加数据导入的困难程度，需要细心的对待和处理，往往有的时候需要对待导入的数据需要“预处理”一下，以便无误的导入到LOB中。

-- The End --

ITPub博客

【实验】【SQL*Loader】使用SQLLDR将数据加载到CLOB字段

最新文章