2010年10月9日土曜日

sqlplusでBLOBデータの参照

BLOBデータ型は、通常のsql実行では参照することができません。なのでわざわざ、BLOBの項目をselect句の取得項目から削除したりしてます。

どうしてもお手軽にsqlplusからblobのデータを参照したいことがあると思います。
そんなときは、デフォルトで提供されているパッケージを使うとそれなりに表示することができます。

テーブル定義

15:56:46 SQL> desc blob_test
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL CHAR(2)
 BLOB_DATA                                          BLOB

BLOBカラムを含めてselectを実行

SQL> select * from blob_test;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus

blob型カラムがあるのでエラーになります。
日本語メッセージだとこんな感じになります。
SP2-0678: 列または属性型はSQL*Plusでは表示できません。

最近知ったけど、Oracle11gのsqlplusだとこの問題が解決されてる。
こんな感じに16進数でダンプされるんだよね。やるなOracle。
16:20:37 SQL> select * from blob_test;

ID
--
BLOB_DATA
----------------------------------------------------------------------------------------------------
01
333435

02
44462D312E340D25E2E3CFD30D0A32303037352030206F626A0D3C3C2F4C696E656172697A656420312F4C20313030393131
35342F4F2032303037382F4520313439353439322F4E20313330302F5420

また、sqlplusコマンドのlobofを使うとダンプ開始位置を任意に変えられるようになってます。
マニュアルは、こちら。Oracleマニュアル
set lobof 100

sqlplusでBLOBを参照する方法

Oracle10gのクライアントの場合は、下記の方法でBLOBデータを参照する必要がある。

DBMS_LOBパッケージ

dbms_lobパッケージのsubstr関数を使うことによって、16進数でダンプできます。
ただし、上限は2000バイト(raw型の上限にひっかかるのかな?)なので、でかいサイズだと分割しないといけなかったりします。

この例だと、一括で前バイト数をだんぷしていますが、
dbms_lob.substr(カラム名, 読み込むバイト数, 読み込み開始バイト位置)
としてあげると任意のバイトデータをダンプできます。

16:30:25 SQL> r
  1* select dbms_lob.substr(blob_data, dbms_lob.getlength(blob_data)) from blob_test where id = '01'

DBMS_LOB.SUBSTR(BLOB_DATA,DBMS_LOB.GETLENGTH(BLOB_DATA))
----------------------------------------------------------------------------------------------------
3132333435

utl_rawパッケージ

utl_rawパッケージを使うと、文字列などに変換して出力できます。
dbms_lobと同じように上限は2000バイトみたいです。

0x3132333435をダンプしてるので、12345となります。
16:35:41 SQL> select utl_raw.cast_to_varchar2(blob_data) from blob_test where id = '01';

UTL_RAW.CAST_TO_VARCHAR2(BLOB_DATA)
----------------------------------------------------------------------------------------------------
12345