【實驗】【SQL*Loader】使用SQLLDR將資料載入到CLOB欄位

secooler發表於2009-08-31
SQL
上文提到，使用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部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-613651/，如需轉載，請註明出處，否則將追究法律責任。

相關文章