OracleDB BLOB の読み書き

試してみた。

BLOBデータの登録
BLOBデータを読み出してファイルに出力する

基本的な読み書き

ディレクトリの登録と権限の付与。

$ sqlplus sys/passwd@xe as sysdba

create directory LOB_DIR as '/tmp';
grant read on directory LOB_DIR to hokorobi;
grant write on directory LOB_DIR to hokorobi;
grant execute on sys.utl_file to public;

テーブル準備

$ sqlplus hokorobi/passwd@xe

create table blob_sample (
	blob_id char(3) primary key,
	blob_data blob
);

書き込み

declare
	infilename varchar2(100) := 'sample01.jpg';
	infile bfile := bfilename( 'LOB_DIR', infilename);
	v_blob_data blob;
begin
	insert into blob_sample values( 'B01', empty_blob())
		returning blob_data into v_blob_data;
	dbms_lob.fileopen( infile, dbms_lob.file_readonly);
	dbms_lob.loadfromfile( v_blob_data, infile, dbms_lob.getlength( infile ));
	dbms_lob.fileclose( infile );
	commit;
end;
/

書き出し

declare
	v_blob_data blob;
	v_blob_buffer long raw;
	v_file_size integer;
	v_file_handle utl_file.file_type;
	v_start_point integer := 1;
	v_write_size integer := 2000;
begin
	select blob_data into v_blob_data from blob_sample where blob_id = 'B01';
	v_file_handle := utl_file.fopen('LOB_DIR', 'sample02.jpg', 'wb');
	v_file_size := dbms_lob.getlength(v_blob_data);
	while v_start_point < v_file_size loop
		if v_start_point + v_write_size > v_file_size then
			v_write_size := v_file_size - v_start_point +1;
		end if;
		dbms_lob.read(v_blob_data, v_write_size, v_start_point, v_blob_buffer);
		utl_file.put_raw(v_file_handle, v_blob_buffer, true);
		v_start_point := v_start_point + v_write_size;
	end loop;
	utl_file.fclose(v_file_handle);
end;
/

ファイル名もテーブルから取得する

PL/SQL はほとんど使ったことがないので練習も兼ねて。

テーブル作成

create table blob_sample2 (
	blob_id varchar(3) primary key,
	blob_data blob,
	blob_name varchar(200)
);

書き込み

declare
	infilename varchar2(100) := 'sample01.jpg';
	infile bfile := bfilename( 'LOB_DIR', infilename);
	v_blob_data blob;
begin
	insert into blob_sample2 values( 'B01', empty_blob(), infilename)
		returning blob_data into v_blob_data;
	dbms_lob.fileopen( infile, dbms_lob.file_readonly);
	dbms_lob.loadfromfile( v_blob_data, infile, dbms_lob.getlength( infile ));
	dbms_lob.fileclose( infile );
	commit;
end;
/

書き出し

declare
	v_blob_data blob;
	v_blob_name varchar(100);
	v_blob_buffer long raw;
	v_file_size integer;
	v_file_handle utl_file.file_type;
	v_start_point integer := 1;
	v_write_size integer := 2000;
begin
	select blob_data, blob_name into v_blob_data, v_blob_name from blob_sample2 where blob_id = 'B01';
	v_file_handle := utl_file.fopen('LOB_DIR', v_blob_name, 'wb');
	v_file_size := dbms_lob.getlength(v_blob_data);
	while v_start_point < v_file_size loop
		if v_start_point + v_write_size > v_file_size then
			v_write_size := v_file_size - v_start_point +1;
		end if;
		dbms_lob.read(v_blob_data, v_write_size, v_start_point, v_blob_buffer);
		utl_file.put_raw(v_file_handle, v_blob_buffer, true);
		v_start_point := v_start_point + v_write_size;
	end loop;
	utl_file.fclose(v_file_handle);
end;
/

一括で書き出し

一緒に blob_id と blob_name を CSV で出力。

declare
	v_blob_buffer long raw;
	v_file_size integer;
	v_dummy_file_size integer;
	v_dummy_block_size integer;
	file_chk boolean;
	v_file_handle utl_file.file_type;
	v_file_name varchar2(100);
	v_start_point integer;
	v_write_size integer;
	cursor cu is select * from blob_sample2;
	v_rec blob_sample2%rowtype;
	prefix integer;
	-- CSV 出力用
	fno utl_file.file_type;
begin
	fno := utl_file.fopen('LOB_DIR', 'output.csv', 'W', 2002);
	open cu;
	loop
		fetch cu into v_rec;
		exit when cu%notfound;
		v_file_name := v_rec.blob_name;
		prefix := 0;
		-- ファイル存在チェック
		utl_file.fgetattr('LOB_DIR', v_file_name, file_chk, v_dummy_file_size, v_dummy_block_size);
		while file_chk loop
			prefix := prefix + 1;
			v_file_name := prefix || '_' || v_rec.blob_name;
			utl_file.fgetattr('LOB_DIR', v_file_name, file_chk, v_dummy_file_size, v_dummy_block_size);
		end loop;
		-- ファイル出力
		v_file_handle := utl_file.fopen('LOB_DIR', v_file_name, 'wb');
		v_file_size := dbms_lob.getlength(v_rec.blob_data);
		-- dbms_output.put_line(v_file_size);
		v_start_point := 1;
		v_write_size := 30720;
		while v_start_point < v_file_size loop
			if v_start_point + v_write_size > v_file_size then
				v_write_size := v_file_size - v_start_point +1;
			end if;
			dbms_lob.read(v_rec.blob_data, v_write_size, v_start_point, v_blob_buffer);
			utl_file.put_raw(v_file_handle, v_blob_buffer, true);
			v_start_point := v_start_point + v_write_size;
		end loop;
		utl_file.fclose(v_file_handle);
		utl_file.putf(fno, '%s,%s\n', v_rec.blob_id, v_file_name);
	end loop;
	close cu;
	utl_file.fclose(fno);
end;
/

CSV を外部表にしてファイルを登録

SHIFT the Oracle 外部表

DROP TABLE SAMPLE;
CREATE TABLE SAMPLE (
	id VARCHAR2(10),
	name varchar2(10)
	)
ORGANIZATION EXTERNAL (
	TYPE ORACLE_LOADER
	DEFAULT DIRECTORY LOB_DIR
	ACCESS PARAMETERS (
		RECORDS DELIMITED BY NEWLINE
		FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
		(
			id,
			name
		)
	)
	LOCATION (
		'hoge.csv'
	)
);

select したらエラー発生。name の桁が足りなかった。
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52

一括書き込み

declare
	infilename varchar2(10);
	infile bfile;
	v_blob_data blob;
	cursor cu is select * from sample;
	rec sample%rowtype;
begin
	open cu;
	loop
		fetch cu into rec;
		exit when cu%notfound;
		infile := bfilename('LOB_DIR', rec.name);
		insert into blob_sample2 values(rec.id, empty_blob(), rec.name)
			returning blob_data into v_blob_data;
		dbms_lob.fileopen( infile, dbms_lob.file_readonly);
		dbms_lob.loadfromfile( v_blob_data, infile, dbms_lob.getlength( infile ));
		dbms_lob.fileclose( infile );
	end loop;
	close cu;
end;
/

hoge.csv の改行コードが だったのでファイルが見つからなかった。sample.name の値が 01.jpg などとなっていたため。改行コードを に直した。Oracle が動いている OS が Windows の場合はどうなるんだろう？