サイトアイコン こじりふぁ

ファイルをBLOBカラムに登録

DBサーバー上にあるファイルをテーブルのBLOBカラムに登録するサンプルになります。

BLOBカラムにファイルを登録する場合、javaなどPL/SQL以外で登録するのが一般的な気がしますが、DBサーバを使ってデータ移行を行う際や、試験フェーズなどで、テストデータを効率よく登録したいという場合に使えるかと思います。

DBサーバに配置されたファイルをテーブルのBLOBカラムに登録するPL/SQL

TB_SAMPLEテーブルのBLOB_DATAカラムに「sample.txt」を登録しています。

DECLARE
  wk_blob   BLOB;
  wk_bfile  BFILE;
BEGIN

  --空のBLOBデータを登録
  INSERT INTO
    TB_SAMPLE (ID, BLOB_DATA)
  VALUES
    (1, empty_blob())
  RETURN BLOB_DATA INTO wk_blob
  ;

  -- BFILENAMEにディレクトリオブジェクト名と対象ファイルを指定して、BFILEオブジェクトを取得
  -- ※「SAMPLE_DIR」は自分の環境で作成しているディレクトリオブジェクトを使用して下さい
  -- ※「sample.txt」は任意のファイルをDBサーバ上の「SAMPLE_DIR」が定義しているディレクトリに配置しておくこと
  wk_bfile := BFILENAME('SAMPLE_DIR', 'sample.txt');

  -- ファイルを読み込みでオープン
  DBMS_LOB.FILEOPEN(wk_bfile, DBMS_LOB.FILE_READONLY);

  -- ファイルをwk_bfileを経由してTB_SAMPLEテーブルのBLOB_DATAカラムにロード
  DBMS_LOB.LOADFROMFILE(wk_blob, wk_bfile, DBMS_LOB.GETLENGTH(wk_bfile));

  -- ファイルをクローズ
  DBMS_LOB.FILECLOSE(wk_bfile);

  COMMIT;

END;
/

PL/SQLプロシージャが正常に完了しました。

BLOBカラムに登録されたファイルをDBサーバに出力

TB_SAMPLEテーブルのBLOB_DATAカラムに格納されているファイルデータを「sample_output.txt」として出力しています。

「sample.txt」と「sample_output.txt」は同じ内容になっていることが確認できると思います。

DECLARE

  wk_file_handle UTL_FILE.FILE_TYPE;
  wk_file_size  INTEGER;
  wk_start_size INTEGER := 1;
  wk_write_size INTEGER := 2000;
  wk_raw  LONG RAW;
  wk_blob BLOB;

BEGIN

  -- BLOBデータをテーブルから取得
  SELECT
    BLOB_DATA
  INTO
    wk_blob
  FROM
    TB_SAMPLE
  WHERE
    ID = 1
  ;

  -- 出力ファイル「sample_output.txt」を"wb"モードでオープンする
  -- 「SAMPLE_DIR」はディレクトリオブジェクト
  wk_file_handle := UTL_FILE.FOPEN('SAMPLE_DIR', 'sample_output.txt', 'wb');

  -- BLOBデータのサイズを取得する
  wk_file_size := DBMS_LOB.GETLENGTH(wk_blob);

  -- 2000バイトずつ出力する
  WHILE wk_start_size < wk_write_size LOOP

    -- 最後の書込みの場合、サイズを調整する
    IF wk_start_size + wk_write_size > wk_file_size THEN
      wk_write_size := wk_file_size - wk_start_size + 1;
    END IF;

    -- BLOBデータを先頭から読み込んで「wk_raw」に格納
    DBMS_LOB.READ(wk_blob, wk_write_size, wk_start_size, wk_raw);

    -- 「wk_raw」に代入されたBLOBデータをファイル出力する
    UTL_FILE.PUT_RAW(wk_file_handle, wk_raw, true);

    -- 書込み開始位置を加算する
    wk_start_size := wk_start_size + wk_write_size;

  END LOOP;

  -- 解放処理
  UTL_FILE.FCLOSE(wk_file_handle);

END;
/

PL/SQLプロシージャが正常に完了しました。