SQL*Loader で BLOBロード Tweet

少々前に、大量のBLOBデータをデータベースへロードするためのプログラムをコーディングしている方がいたのだが、そのプログラムの仕様を聞いてみたら、SQL*Loaderでも対応可能な内容だった。SQL*LoaderでBLOBデータをローディングできることは知らなかったのだ。知っていたらもっと楽だったろうに。

今回の例ではOracle10g R1を利用しているのだが、Oracle10g R2でも使い方は同じ。

●　参考：
・Oracle Database ユーティリティ　10g リリース1（10.1）
・Oracle Database ユーティリティ　10g リリース2（10.2）LOBFILEからのLOBデータロード
・Oracle Database ユーティリティ　10g リリース2（10.2）EXPRESSIONパラメータ

ということで、前述の話に比べると少々単純なのだがロードの例を一つ。

●　まずは準備。

BLOBを格納する表のキーはユニークな数値なので、シーケンスを利用する。え！？　SQL*Loaderでシーケンスが利用できるのか？
できるんです。EXPRESSIONパラメータを利用すれば！（EXPRESSIONパラメータの利用例は随分前にOTN-JのCode Tipsへも投稿してあったっけ。OTN-JのCode Tipsを参照するにはOTN-Jへのユーザ登録（無料）必要です。）

> conn scott/tiger
接続されました。
SCOTT> 
SCOTT> 
SCOTT> create sequence sq_blob_key
  2  start with 1 
  3  increment by 1
  4  maxvalue 999999
  5  nocycle
  6  nocache;

順序が作成されました。

BLOBデータを登録する表の定義は以下。
（blob用表領域は他のデーブルデータとは別表領域にしている）

SCOTT> l
  1  create table blobtab
  2  (
  3    blob_id number primary key,
  4    blob_type varchar(4),
  5    content blob
  6  )
  7  lob(content) store as
  8  (
  9    tablespace blobspace
 10    nocache logging
 11* )
SCOTT> /

表が作成されました。

SCOTT> 

●　SQL*Loaderの制御ファイルとBLOBとして登録するファイルは同一ディレクトリ上へ配置。

ちなみに、Linux/Unix系OSでは、hostコマンドまたは、! を利用すればSQL*Plusからshellコマンドなとを直接実行できる。

SCOTT>  
SCOTT> !ls | grep -E '¥.(ctl|jpg|zip|png|gif)$'
blobload.ctl
discus.gif
generic_connectivity.zip
image001.png
neontetra.jpg

SCOTT> 

●　SQL*Loaderの制御ファイルの内容

ポイントは、シーケンスを利用するために、expressionパラメータで　sq_blob_key.nextval と設定している点と、ex_fname FILLER以降の定義（赤字部分）。

SCOTT> !cat blobload.ctl
LOAD DATA
INFILE 'blobload.dat'
INTO TABLE blobtab
FIELDS TERMINATED BY ','
(
  blob_id expression "sq_blob_key.nextval",
  blob_type,
  ext_fname FILLER CHAR(80),
  content LOBFILE(ext_fname) TERMINATED BY EOF
)

●　SQL*Loaderの制御ファイルのINFILEパラメータで指定するデータファイルの内容

この例では同一ディレクトリに配置した gif/zip/png/jpgを登録対象とした。
拡張子、ファイル名の順にカンマ区切りでデータファイルを作成。（awkを使えば簡単ですしね）

SCOTT> !ls | grep -E '¥.(zip|jpg|png|gif)$' | awk '{print substr($1,index($1,".")+1)","$1","}' > blobload.dat
SCOTT> !cat blobload.dat
gif,discus.gif,
zip,generic_connectivity.zip,
png,image001.png,
jpg,neontetra.jpg,

SCOTT> 
SCOTT>  

●　準備完了！

準備が整った。今回はSQL*Loaderの制御ファイル、データファイル、BLOBとして登録するファイルを同一ディレクトリに配置した。

SCOTT> !ls | grep -E '¥.(ctl|dat|jpg|zip|png|gif)$'
blobload.ctl
blobload.dat
discus.gif
generic_connectivity.zip
image001.png
neontetra.jpg

SCOTT> 

●　SQL*Loaderの実行

SCOTT> !sqlldr userid=scott/tiger@xtiger control=blobload.ctl log=blobload.log

SQL*Loader: Release 10.1.0.3.0 - Production on 土 9月 22 17:12:16 2007

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

コミット・ポイントに達しました。 - 論理レコード件数4

SCOTT>

●　SQL*Loaderのログを確認！

SCOTT> !cat blobload.log

SQL*Loader: Release 10.1.0.3.0 - Production on 土 9月 22 17:12:16 2007

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

制御ファイル:    blobload.ctl
データ・ファイルblobload.dat
不良ファイル:  blobload.bad
廃棄ファイル:  指定なし

 (すべて廃棄できます)

ロード数: ALL
スキップ数: 0
許容エラー数: 50
バインド配列: 64行、最大256000バイト
継続文字:    指定なし
使用パス:      従来型

表BLOBTAB、 ロード済 すべての論理レコードから
この表に対する有効な挿入オプション: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
BLOB_ID                                                   EXPRESSION
    列のSQL文字列 : "sq_blob_key.nextval"
BLOB_TYPE                          FIRST    *  ,     CHARACTER            
EXT_FNAME                           NEXT   80  ,     CHARACTER            
  (FILLERフィールド)
CONTENT                          DERIVED    * EOF    CHARACTER            
    動的LOBFILE - ファイル名EXT_FNAME


表BLOBTAB:
  4行のロードに成功しました。
  0行はデータ・エラーのためロードされませんでした。
  0行はWHEN句のエラーのためロードされませんでした。
  0行はすべてのフィールドがNULLのためロードされませんでした。

バインド配列に割り当てられた領域:    21760バイト(64行)
読取りバッファのバイト数:  1048576

スキップされた論理レコードの合計:           0
読み込まれた論理レコードの合計:             4
拒否された論理レコードの合計:               0
廃棄された論理レコードの合計:              0

実行開始土 9月  22 17:12:16 2007
実行終了土 9月  22 17:12:17 2007

実行時間:        00: 00: 00.80
CPUタイム :      00: 00: 00.06

SCOTT> 

●　内容確認。(登録ファイルのサイズとBLOBサイズが同じであることを確認）

SCOTT> select
  2    blob_id,
  3    blob_type,
  4    lengthb(content)
  5  from
  6    blobtab
  7  order by 
  8    blob_id;

   BLOB_ID BLOB LENGTHB(CONTENT)
---------- ---- ----------------
         1 gif              9675
         2 zip             20070
         3 png           2327573
         4 jpg              6492

SCOTT> 
SCOTT> !ls -l | grep -E '¥.(jpg|zip|png|gif)$'
-rw-r--r--   1 oracle  oinstall     9675 Dec 31  1997 discus.gif
-rw-r--r--   1 oracle  oinstall    20070 Nov 17  2005 generic_connectivity.zip
-rw-r--r--   1 oracle  oinstall  2327573 Jul 21 16:21 image001.png
-rw-r--r--   1 oracle  oinstall     6492 May  4  1999 neontetra.jpg

SCOTT>