BLOBを文字列で出力する

こじ

5年前

テーブルに登録されているBLOBデータを出力するサンプルになります。

BLOBを通常の文字列で出力するには「UTL_RAW.CAST_TO_VARCHAR2」や「DBMS_LOB.SUBSTR」を使う必要があります。

BLOBカラムをそのままSELECT

Oracle12cで、BLOBカラムをそのまま出力すると16進数で出力されます。

16進数で出力されても読めないので使いものになりません。

SELECT BLOB_DATA FROM TB_SAMPLE;

BLOB_DATA
--------------------------------
E38182E38184E38186E38188E3818A

UTL_RAW.CAST_TO_VARCHAR2

「UTL_RAW.CAST_TO_VARCHAR2」を使うと、16進数表示から通常の文字列に変換することができます。

SELECT UTL_RAW.CAST_TO_VARCHAR2(BLOB_DATA) FROM TB_SAMPLE;

UTL_RAW.CAST_TO_VARCHAR2(BLOB_DATA)
------------------------------------
あいうえお

DBMS_LOB.SUBSTR

「UTL_RAW.CAST_TO_VARCHAR2」で2000バイトを超えるとデータを使用するとエラーが発生します。その場合は「DBMS_LOB.SUBSTR」を使って出力文字数を制御しつつ出力することができます。

「DBMS_LOB.SUBSTR」の第1引数は出力バイト数、第2引数は開始バイト数を指定します。

SELECT LENGTHB(BLOB_DATA) FROM TB_SAMPLE;

LENGTHB(BLOB_DATA)
------------------
              2004

SELECT UTL_RAW.CAST_TO_VARCHAR2(BLOB_DATA) FROM TB_SAMPLE;

行1でエラーが発生しました。:
ORA-22835: CLOBからCHAR、またはBLOBからRAWへの変換には、バッファーが小さすぎます(実際: 2004、最大: 2000)

SELECT
     UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB_DATA,1998,1))
  || UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB_DATA,2000,1999))
FROM
    TB_SAMPLE;

----------------------------------------
あいうえお　～～～　省略　～～～

不完全なマルチバイト文字

「DBMS_LOB.SUBSTR」を使って全角文字を切り出すとエラーになるケースがあります。

私の環境では全角文字は1文字3バイトなので、BLOBカラムに全て全角文字が格納されている状態で、「DBMS_LOB.SUBSTR」を使って2000バイトで切り出すと最後の1文字が分断されるため下記のエラーが発生します。

SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB_DATA,2000,1)) FROM TB_SAMPLE;

ERROR:
ORA-29275: 不完全なマルチバイト文字です

結論としては、BLOBカラムに全角文字を入れることは普通にありえることなので、SQLの「UTL_RAW.CAST_TO_VARCHAR2」、「DBMS_LOB.SUBSTR」のみを使って、BLOBデータを出力することは難しいように思います。

BLOBデータを扱うときは素直にPL/SQLを使う方がよいかなぁっと思います。なんか良い方法あるのかなぁ。