【Oracle】PLSQLでBLOBデータを出力

PLSQLでBLOBデータを出力します。

CREATE OR REPLACE PROCEDURE testBlobOut (kanriNo IN VARHCAR) AS
CURSOR C_CONTROL IS
  SELECT
    TB1.IMAGE_NAME, 
    TB1.IMAGE
  FROM
    SAMPLE TB1
  WHERE
    TB1.KANRI_NO = kanriNo;

v_blob_locater BLOB;
v_offset INTEGER := 1;
v_buffer LONG RAW;
v_file_buffer_size INTEGER := 32000;
v_amount INTEGER := 32000;
v_totalsize INTEGER;
v_filetype UTL_FILE.FILE_TYPE;
v_filename VARCHAR2(1000) := '';
v_openmode VARHCAR2(2) := 'wb';
v_dir VARCHAR2(1000) := 'C/temp';

BEGIN
  OPEN C_CONTROL;
  LOOP
    FETCH C_CONTROL INTO v_blob_locater, v_filename;
    EXIT WHEN C_CONTROL%NOTFOUND;
    DBMS_OUTPUT.PUTLINE('fileName:' || v_filename);
    v_totalsize := DBMS_LOB.GETLENGTH(v_blob_locater);
    v_filetype := UTL_FILE.FOPEN(
      v_dir,
      v_filename,
      v_openmode,
      v_file_buffer_size
    );
    while v_offset < v_totalsize loop
      if v_offset + v_amount > v_totalsize then
        v_amount := v_totalsize - v_offset + 1;
      end if;
      DBMS_LOB.READ(
        v_blob_locater,
        v_amount,
        v_offset,
        v_buffer
      );
      UTL_FILE.PUT_RAW(
        v_filetype,
        v_buffer,
        true
      );
      v_offset := v_offset + v_amount;
      DBMS_OUTPUT.PUT_LINE('Offset :' || v_offset);
    end loop;
    UTL_FILE.FFLUSH(v_filetype);
    UTL_FILE.FCLOSE(v_filetype);

    DBMS_OUTPUT.PUT_LINE('fileSize :' || v_totalsize);
  END LOOP;
  CLOSE C_CONTROL;
EXCEPTION WHEN OTHERS THEN
  UTL_FILE.FCLOSE_ALL;
  RAISE;
END;
/

PLSQLの実行方法

set serveroutput ON
execute testBlobOut('000010');