この記事は約36分22秒で読むことができます。

blob バイナリ複製&バイナリ取込 with plsql

まえがき

おもしろそうだったので触れてみた!!!面白かった!!仕事疲れたけど、気晴らしに触れてみた!まぁ余計に疲れるけどね、よいのです、そんなことは。

参考文献

事の発端

UTL_FILE.FCOPY()とかFRENAME()とか  

clobだったら、LOADCLOBFROMFILE

LOADBLOBFROMFILEプロシージャ  

オープンする

FILEOPENプロシージャ  

クローズする

FILECLOSEプロシージャ  

ファイルをインスタンス化するてきな感じ。メモリ上にオブジェクト作るかんじかな。

BFILENAME  

DBMSLOB.SQLパッケージで定義される定数の一つlobmaxsize INTEGER 18446744073709551615

DBMS_LOB.LOBMAXSIZE  

コストラクタ的なやつ

EMPTY_BLOB、EMPTY_CLOB  

clob型にぶち込むときに文字コード指定したりするやつ。

NLS_CHARSET_ID  

バッファにためて

GET_RAWファンクション  

ためたバッファをファイルに吐く。んで、バッファフラッシュ。ファイルから読み込んで取得したバイナリデータをメモリにのせてファイルに吐いた後、バッファをクリーンするイメージかな。

PUT_RAWプロシージャ  

事前準備

OSのディレクトリをオラクルアプリが認識できるようにマウントさせる。sqlでそういうことできるんだもんな。べんりだな。

コード表示

--sys session
CREATE OR REPLACE DIRECTORY dp_in_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/in';
GRANT READ ON DIRECTORY dp_in_aine_dir TO aine;
GRANT WRITE ON DIRECTORY dp_in_aine_dir TO aine;

--aine session with dba role

select * from dba_directories;
select * from dba_tab_privs where table_name = 'DP_IN_AINE_DIR';

コンスト定義

定義したらいい

コード表示

create or replace package const___symbolic_character___
as
	comma___ constant varchar2(100) := ',';
	coron___ constant varchar2(100) := ':';
    dot___ constant varchar2(100) := '.';
	equal___ constant varchar2(100) := '=';
	left_parentheses___ constant varchar2(100) := '(';
	right_parentheses___ constant varchar2(100) := ')';
	semi_coron___ constant varchar2(100) := ';';
	single_quote___ constant varchar2(100) := '''';
	space___ constant varchar2(100) := ' ';
    under_score___ constant varchar2(100) := '_';
    function of___comma___(p_comma___ varchar2 := comma___) return varchar2;
    function of___coron___(p_coron___ varchar2 := coron___) return varchar2;
	function of___dot___(p_dot___ varchar2 := dot___) return varchar2;
    function of___equal___(p_equal___ varchar2 := equal___) return varchar2;
    function of___left_parentheses___(p_left_parentheses___ varchar2 := left_parentheses___) return varchar2;
    function of___right_parentheses___(p_right_parentheses___ varchar2 := right_parentheses___) return varchar2;
    function of___semi_coron___(p_semi_coron___ varchar2 := semi_coron___) return varchar2;
    function of___single_quote___(p_single_quote___ varchar2 := single_quote___) return varchar2;
    function of___space___(p_space___ varchar2 := space___) return varchar2;
    function of___under_score___(p_under_score___ varchar2 := under_score___) return varchar2;
end;
/
create or replace package body const___symbolic_character___
is
    function of___comma___(p_comma___ varchar2) return varchar2 is rt varchar2(100) := p_comma___; begin return rt; end; 
    function of___coron___(p_coron___ varchar2) return varchar2 is rt varchar2(100) := p_coron___; begin return rt; end; 
    function of___dot___(p_dot___ varchar2) return varchar2 is rt varchar2(100)  :=  dot___; begin return rt; end; 
    function of___equal___(p_equal___ varchar2) return varchar2 is rt varchar2(100) := p_equal___; begin return rt; end; 
    function of___left_parentheses___(p_left_parentheses___ varchar2) return varchar2 is rt varchar2(100) := p_left_parentheses___; begin return rt; end; 
    function of___right_parentheses___(p_right_parentheses___ varchar2) return varchar2 is rt varchar2(100) := p_right_parentheses___; begin return rt; end; 
    function of___semi_coron___(p_semi_coron___ varchar2) return varchar2 is rt varchar2(100) := p_semi_coron___; begin return rt; end; 
    function of___single_quote___(p_single_quote___ varchar2) return varchar2 is rt varchar2(100) := p_single_quote___; begin return rt; end; 
    function of___space___(p_space___ varchar2) return varchar2 is rt varchar2(100) := p_space___; begin return rt; end;
    function of___under_score___(p_under_score___ varchar2) return varchar2 is rt varchar2(100)  :=  under_score___; begin return rt; end; 
end;
/

ファイル系のコンスト定義

定義したらいい

コード表示

create or replace package const___file_usage___
is
    prefix_file_name___ constant varchar2(100):='dup';
    file_png_extension___ constant varchar2(100):='png';
    file_gif_extension___ constant varchar2(100):='gif';
    file_jpg_extension___ constant varchar2(100):='jpg';
    file_jpeg_extension___ constant varchar2(100):='jpeg';
    file_mp3_extension___ constant varchar2(100):='mp3';
    function of___prefix_file_name___(p_prefix_file_name___ varchar2 := prefix_file_name___) return varchar2;
    function of___file_png_extension___(p_file_png_extension___ varchar2 := file_png_extension___) return varchar2;
    function of___file_gif_extension___(p_file_gif_extension___ varchar2 := file_gif_extension___) return varchar2;
    function of___file_jpg_extension___(p_file_jpg_extension___ varchar2 := file_jpg_extension___) return varchar2;
    function of___file_jpeg_extension___(p_file_jpeg_extension___ varchar2 := file_jpeg_extension___) return varchar2;
    function of___file_mp3_extension___(p_file_mp3_extension___ varchar2 := file_mp3_extension___) return varchar2;
end;
/
create or replace package body const___file_usage___
is
    function of___prefix_file_name___(p_prefix_file_name___ varchar2) return varchar2 is rt varchar2(100) := prefix_file_name___; begin return rt; end; 
    function of___file_png_extension___(p_file_png_extension___ varchar2) return varchar2 is rt varchar2(100) := file_png_extension___; begin return rt; end; 
    function of___file_gif_extension___(p_file_gif_extension___ varchar2) return varchar2 is rt varchar2(100) := file_gif_extension___; begin return rt; end; 
    function of___file_jpg_extension___(p_file_jpg_extension___ varchar2) return varchar2 is rt varchar2(100) := file_jpg_extension___; begin return rt; end; 
    function of___file_jpeg_extension___(p_file_jpeg_extension___ varchar2) return varchar2 is rt varchar2(100) := file_jpeg_extension___; begin return rt; end; 
    function of___file_mp3_extension___(p_file_mp3_extension___ varchar2) return varchar2 is rt varchar2(100) := file_mp3_extension___; begin return rt; end; 
end;
/

スクリプト

ファイルのバイナリは吐き出すと、マウントしたファイルのバイナリーデータはメモリからなくなるから、毎回オープンしてメモリにのせないといけないとおもう。そういうもんじゃないかな。パッケージプロシージャにしました。引数に複製する個数と複製元ファイル名(拡張子除く)と拡張子を指定してgo。ちなみに同じファイル名存在するとora errorなので、take care。ユーザー定義型で例外きってもいいと思う。

コード表示

create or replace package pkg___fio_usage___
is
	procedure proc___dup___(p_dup_cnt number,p_src_file varchar2,p_ext number);
end;
/

create or replace package body pkg___fio_usage___
is
	procedure proc___dup___(p_dup_cnt number,p_src_file varchar2,p_ext number)
	is
		src_file   utl_file.file_type;
		dst_file   utl_file.file_type;
		buffer     raw(32767);
		dup_cnt number := p_dup_cnt;
		src_fnm varchar2(100) := '';
		dup_fnm varchar2(100) := '';
	begin
		loop
			exit when dup_cnt = 0;
			src_fnm := p_src_file
			|| const___symbolic_character___.of___dot___
			|| const___file_usage___.of___file_png_extension___;
			dup_fnm := const___file_usage___.of___prefix_file_name___ 
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___under_score___
			|| p_src_file
			|| const___symbolic_character___.of___under_score___
			|| lpad(dup_cnt,2,0) 
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___dot___
			|| case
					when '01' = lpad(p_ext,2,0) then const___file_usage___.of___file_png_extension___
					when '02' = lpad(p_ext,2,0) then const___file_usage___.of___file_gif_extension___
					when '03' = lpad(p_ext,2,0) then const___file_usage___.of___file_jpg_extension___
					when '04' = lpad(p_ext,2,0) then const___file_usage___.of___file_jpeg_extension___
					when '05' = lpad(p_ext,2,0) then const___file_usage___.of___file_mp3_extension___
					else null
				end
			;
			src_file := utl_file.fopen('DP_IN_AINE_DIR', src_fnm, 'rb', 32767);
			dst_file := utl_file.fopen('DP_IN_AINE_DIR', dup_fnm, 'wb', 32767);
			loop
				begin
					utl_file.get_raw(src_file, buffer, 32767);
					utl_file.put_raw(dst_file, buffer, true);
				exception
					when no_data_found then
						exit;
				end;
			end loop;
			utl_file.fclose(dst_file);
			utl_file.fclose(src_file);
			dup_cnt := dup_cnt - 1;
		end loop;
	end;
end;
/

exec pkg___fio_usage___.proc___dup___(3,'test',1);
/

結果

コピーされたバイナリファイルは644でつかられるぽい。

コード表示

[oracle@f285aba0589a in]$ ll
total 24
-rw-r--r--. 1   1000     1001   61 Mar 31 19:50 tbl___loader___.dat
-rw-r--r--. 1 oracle oinstall 2283 Mar 31 20:05 tbl___loader___.log
-rw-r--r--. 1 oracle oinstall  654 Mar 31 20:05 tbl___loader____18130.log_xt
-rwxrwxrwx. 1 oracle oinstall 9611 Apr  1 07:18 test.png
[oracle@f285aba0589a in]$ ll
total 60
-rw-r--r--. 1 oracle oinstall 9611 Apr  2 00:49 dup___test_01___.png
-rw-r--r--. 1 oracle oinstall 9611 Apr  2 00:49 dup___test_02___.png
-rw-r--r--. 1 oracle oinstall 9611 Apr  2 00:49 dup___test_03___.png
-rw-r--r--. 1   1000     1001   61 Mar 31 19:50 tbl___loader___.dat
-rw-r--r--. 1 oracle oinstall 2283 Mar 31 20:05 tbl___loader___.log
-rw-r--r--. 1 oracle oinstall  654 Mar 31 20:05 tbl___loader____18130.log_xt
-rwxrwxrwx. 1 oracle oinstall 9611 Apr  1 07:18 test.png

作成したファイル取り込もうとおもう

EMPTY_BLOB()してるから必ず、blb_locは0となってポインタは先頭から処理開始するイメージかな。ただ、複製したファイルだとちゃんと取り込めてるかわからんから、異なるバイナリデータを3つ用意してみる。

コード表示

[oracle@f285aba0589a in]$ ll
total 52
-rw-r--r--. 1   1000     1001 4287 Feb 24 13:09 dup___test_01___.png
-rw-r--r--. 1   1000     1001 5221 Feb 25 08:18 dup___test_02___.png
-rw-r--r--. 1   1000     1001 9611 Mar 22 06:46 dup___test_03___.png
-rw-r--r--. 1   1000     1001   61 Mar 31 19:50 tbl___loader___.dat
-rw-r--r--. 1 oracle oinstall 2283 Mar 31 20:05 tbl___loader___.log
-rw-r--r--. 1 oracle oinstall  654 Mar 31 20:05 tbl___loader____18130.log_xt
-rwxrwxrwx. 1 oracle oinstall 9611 Apr  1 07:18 test.png

取込スクリプト

苦戦したのが、dst_oftとsrc_oftの2つには常に1を設定しておきたいが、コンストできないから、変数無駄に増えてしまったこと。うまくできないかなー。

コード表示

drop table tbl___blob___ purge;
create table tbl___blob___ (rn number,blb blob);
select * from tbl___blob___;
desc tbl___blob___;

create or replace procedure proc___imp___(p_dup_cnt number,p_src_file varchar2,p_ext number)
as
	bfl_pnt bfile;
	blb_loc blob;
	rn number;
	dst_oft number := 1;--not allowable constant variable
	src_oft number := 1;--not allowable constant variable
	tmp_dst_oft constant number := 1;--error escape
	tmp_src_oft constant number := 1;--error escape
	dup_cnt number := p_dup_cnt;
	src_fnm varchar2(100) := '';
begin
	loop
		exit when dup_cnt = 0;
		src_fnm := const___file_usage___.of___prefix_file_name___ 
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___under_score___
		|| p_src_file
		|| const___symbolic_character___.of___under_score___
		|| lpad(dup_cnt,2,0) 
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___dot___
		|| case
				when '01' = lpad(p_ext,2,0) then const___file_usage___.of___file_png_extension___
				when '02' = lpad(p_ext,2,0) then const___file_usage___.of___file_gif_extension___
				when '03' = lpad(p_ext,2,0) then const___file_usage___.of___file_jpg_extension___
				when '04' = lpad(p_ext,2,0) then const___file_usage___.of___file_jpeg_extension___
				when '05' = lpad(p_ext,2,0) then const___file_usage___.of___file_mp3_extension___
				else null
			end
		;
		dbms_output.put_line(src_fnm);
		bfl_pnt := bfilename('DP_IN_AINE_DIR', src_fnm);
		select nvl(max(rn), 0) + 1 into rn from tbl___blob___;
		insert into tbl___blob___ values (rn, empty_blob()) returning blb into blb_loc;
		begin
			dbms_lob.fileopen(bfl_pnt, dbms_lob.file_readonly);
		exception when utl_file.invalid_operation then
			rollback;
			exit;
		end;
		dbms_output.put_line ( 'writable size : ' || dst_oft || 'bytes' );
		dbms_lob.loadblobfromfile (
		   blb_loc
		   ,bfl_pnt
		   ,dbms_lob.lobmaxsize
		   ,dst_oft
		   ,src_oft);
		commit;
		dbms_output.put_line ( 'writable size : ' || dst_oft || 'bytes' );
		dbms_lob.fileclose(bfl_pnt);
		dup_cnt := dup_cnt - 1;
		src_oft := tmp_src_oft;
		dst_oft := tmp_dst_oft;
	end loop;
end;
/

exec proc___imp___(3,'test',1);
/

実行ログ

できてそう

コード表示

dup___test_03___.png
writable size : 1bytes
writable size : 9612bytes
dup___test_02___.png
writable size : 1bytes
writable size : 5222bytes
dup___test_01___.png
writable size : 1bytes
writable size : 4288bytes


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


結果

はじめてつくったけど、面白いねgif。ちょっとゆがんでるけど
。ご愛嬌ってことで。

Leave a Reply

Your email address will not be published. Required fields are marked *