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');