BLOBデータをSPOOL

こじ

5年前

TB_SAMPLEテーブルのBLOBカラムに格納されているバイナリデータを文字列に変換して出力するPL/SQLサンプルです。

文字列で出力する関係上、CLOBに変換してからDBMS_OUTPUT.PUT_LINEを使って出力しています。

BLOBカラムには3行分の文字列データが格納されていて、BLOBのデータ部分だけを出力するために、PL/SQLはSQLファイルで用意し、スクリプト実行をしています。

BLOBカラムのデータをSPOOLで出力

PL/SQLのSQLファイルを用意

下記のPL/SQLコードが記載された「sample.sql」を用意する。

SET LINESIZE 32767
SET PAGESIZE 0
SET HEADING OFF
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET ECHO OFF
SET RECSEP OFF
SET LONG 1000000
SET LONGC 1000000
SET SERVEROUTPUT ON

SPOOL sample.csv

DECLARE
  i_amount       INTEGER := DBMS_LOB.LOBMAXSIZE;
  i_dest_offset  INTEGER := 1;
  i_src_offset   INTEGER := 1;
  i_default_csid INTEGER := DBMS_LOB.DEFAULT_CSID;
  i_lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
  o_warning      INTEGER;
  i_blob         BLOB;
  o_clob         CLOB;
BEGIN

  SELECT
    BLOB_DATA
  INTO
    i_blob
  FROM
    TB_SAMPLE
  WHERE
    ID = 1
  ;

  DBMS_LOB.CREATETEMPORARY(o_clob, TRUE, DBMS_LOB.CALL);
  DBMS_LOB.CONVERTTOCLOB(
    o_clob,          -- 変換先（CLOB）
    i_blob,          -- 変換元（BLOB）
    i_amount,        -- 変換する文字数
    i_dest_offset,   -- 変換先（CLOB）への書き込み開始位置
    i_src_offset,    -- 変換元（BLOB）への変換開始位置
    i_default_csid,  -- 変換元（BLOB）の文字セットID
    i_lang_context,  -- とりあえずデフォルト値を渡す
    o_warning        -- 警告あれば返却されるらしい
  );

  -- 各行にCRLFの改行コードが付いているデータを出力するが、
  -- SPOOLすると不要な空行が自動で付くのを回避するためにCRを削除している
  -- 他に良い方法があるかもしれない
  DBMS_OUTPUT.PUT_LINE(REPLACE(o_clob, CHR(13), NULL));

END;
/

SPOOL OFF

sample.sqlを実行

SQL> @sample.sql
BLOBカラムのデータ（1行目）
BLOBカラムのデータ（2行目）
BLOBカラムのデータ（3行目）

sample.csvの出力結果

BLOBカラムのデータ（1行目）
BLOBカラムのデータ（2行目）
BLOBカラムのデータ（3行目）