PLSQLでBLOBデータを扱う(Oracle) - 闘うITエンジニアの覚え書き

PLSQLでBLOBデータを扱う(Oracle) †

【ファイルから登録】

set serveroutput on

declare
	v_blob_locater  blob;
	v_handle        bfile := bfilename('LOB_DATA_PATH', 'test.jpg');
	v_amount        pls_integer;
	v_doffset       pls_integer := 1;
	v_soffset       pls_integer := 1;
begin
	dbms_lob.fileopen(v_handle, dbms_lob.file_readonly);

	v_amount := dbms_lob.getlength(v_handle);
	insert into test_blob_data (
		id,
		data
	) values (
		seq_test_blob_data.nextval,
		empty_blob()
	) returning data into v_blob_locater;

	dbms_lob.loadblobfromfile(
		v_blob_locater,
		v_handle,
		v_amount,
		v_doffset,
		v_soffset
	);

	dbms_output.put_line ( '書き込まれたサイズ : ' || v_doffset || 'Bytes' );
	commit;
	dbms_lob.fileclose(v_handle);

end;
/

【ファイルへ読み込み】

set serveroutput on
declare
	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) := 'maguro_out.jpg';
	v_openmode              varchar(2) := 'wb';
begin
	select data into v_blob_locater 
	from test_blob_data where id = 1;

	v_totalsize := dbms_lob.getlength(v_blob_locater);

	v_filetype := utl_file.fopen(
	'LOB_DATA_PATH', 
	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);

end;
/

【blobからclobへ変換するFUNCTION】

/*
 * UTL_RAWなどを使用してBLOBをVARCHAR2に変換すると32K(VARCHAR2の最大長?)以上のデータを
 * 扱えない(エラーになる)為、CLOB<->BLOBの変換を行う独自関数を作成する(パクッったけど..)
 */
CREATE OR REPLACE FUNCTION blob2clob (
   p_blob                     IN       BLOB DEFAULT EMPTY_BLOB ( )
,  p_success                  OUT      VARCHAR2 )
   RETURN CLOB
IS
   bdoc                          BLOB := p_blob;
   cdoc                          CLOB := EMPTY_CLOB ( );
   document                      CLOB := EMPTY_CLOB ( );
   lob_id                        NUMBER;
   amount                        NUMBER;
   bdoc_size                     NUMBER;
   BLOCK                         NUMBER := 10000;
   blockcount                    NUMBER;
   rawbuff                       RAW ( 32000 );
   pos                           NUMBER;
   charbuff                      VARCHAR2 ( 32000 );
   charbuff_size                 NUMBER;
BEGIN
   dbms_lob.createtemporary ( cdoc,TRUE,dbms_lob.CALL );
   bdoc_size := dbms_lob.getlength ( bdoc );

   IF BLOCK < bdoc_size THEN
      blockcount := ROUND ( ( bdoc_size / BLOCK ) + 0.5 );
   ELSE
      blockcount := 1;
   END IF;

   pos := 1;

   FOR i IN 1 .. blockcount LOOP
      dbms_lob.READ ( bdoc,BLOCK,pos,rawbuff );
      charbuff := utl_raw.cast_to_varchar2 ( rawbuff );
      charbuff_size := LENGTH ( charbuff );
      dbms_lob.writeappend ( cdoc,charbuff_size,charbuff );
      pos := pos + BLOCK;
   END LOOP;

   p_success := 'OK';
   RETURN cdoc;

EXCEPTION
   WHEN OTHERS
   THEN
      p_success := 'ERROR::' || SQLCODE || ' -' || SQLERRM;
      RETURN EMPTY_CLOB ( );
END blob2clob;

【clobからblobへ変換するFUNCTION】

/*
 * UTL_RAWなどを使用してBLOBをVARCHAR2に変換すると32K(VARCHAR2の最大長?)以上のデータを
 * 扱えない(エラーになる)為、CLOB<->BLOBの変換を行う独自関数を作成する(パクッったけど..)
 */
create or replace function clob2blob(p_clob clob) return blob as
	v_blob blob;
	v_str varchar2(32767);
	v_offset integer := 1;
	v_length integer;
	v_sum integer;

	begin
		dbms_lob.createTemporary(v_blob, false, dbms_lob.SESSION);
		v_length := dbms_lob.getLength(p_clob);

		loop
			v_sum := 32767;
			dbms_lob.read(p_clob, v_sum, v_offset, v_str);
			dbms_lob.writeAppend(v_blob, v_sum, utl_raw.cast_to_raw(v_str));
			exit when v_offset + v_sum >= v_length;
			v_offset := v_offset + v_sum;
		end loop;

		return v_blob;
	end;
/