LOB型の削除に関して

参考文献

How to drop Oracle LOB  

今の状況

拙作スキーマクリーニングスクリプトですとLOBオブジェクトが残っている。。消さなくてもいいと思うけど、気になったから、調べた。

EXECUTE IMMEDIATE をはじめて書いた。スキーマ内クリーニングスクリプト  

結論

パージすればいい

コード表示

purge recyclebin;

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。ちょっとゆがんでるけど
。ご愛嬌ってことで。

エラーメッセージの実装について with plsql

参考文献

思い通りに作れてよかった

PL/SQL で使用する ユーザー定義例外の宣言と呼び出し

ポイントは一元化

思い通りに作れてよかった

コード表示

set serveroutput on;
create or replace package const___exception_usage___
as
	expt_20000___ exception;
	expt_20001___ exception;
	expt_20002___ exception;
	pragma exception_init(expt_20000___,-20000);
	pragma exception_init(expt_20001___,-20001);
	pragma exception_init(expt_20002___,-20002);
	function of___expt_20000_message___ return varchar2;
	function of___expt_20001_message___ return varchar2;
	function of___expt_20002_message___ return varchar2;
end;
/

create or replace package body const___exception_usage___
as
	function of___expt_20000_message___ return varchar2 as rt varchar2(100) := 'exception_20000'; begin return rt; end; 	
	function of___expt_20001_message___ return varchar2 as rt varchar2(100) := 'exception_20001'; begin return rt; end; 	
	function of___expt_20002_message___ return varchar2 as rt varchar2(100) := 'exception_20002'; begin return rt; end; 	
end;
/

create or replace package const___symbolic_character___
as
	begin___ constant varchar2(100):='begin';
	end___ constant varchar2(100):='end';
	space___ constant varchar2(100):=' ';
	semi_coron___ constant varchar2(100):=';';
	under_score___ constant varchar2(100):='___';
	left_parentheses___ constant varchar2(100):='(';
	right_parentheses___ constant varchar2(100):=')';
	comma___ constant varchar2(100):=',';
    dot___ constant varchar2(100):='.';
    minus___ constant varchar2(100):='-';

    function of___begin___(p_begin___ varchar2:=begin___) return varchar2;
    function of___end___(p_end___ varchar2:=end___) return varchar2;
    function of___space___(p_space___ varchar2:=space___) return varchar2;
    function of___semi_coron___(p_semi_coron___ varchar2:=semi_coron___) return varchar2;
    function of___under_score___(p_under_score___ varchar2:=under_score___) 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___comma___(p_comma___ varchar2:=comma___) return varchar2;
    function of___dot___(p_dot___ varchar2:=dot___) return varchar2;
    function of___minus___(p_minus___ varchar2:=minus___) return varchar2;
end;
/

create or replace package body const___symbolic_character___
as
    function of___begin___(p_begin___ varchar2) return varchar2 is rt varchar2(100):= p_begin___; begin return rt; end; 
    function of___end___(p_end___ varchar2) return varchar2 is rt varchar2(100):= p_end___; begin return rt; end; 
    function of___space___(p_space___ varchar2) return varchar2 is rt varchar2(100):= p_space___; 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___under_score___(p_under_score___ varchar2) return varchar2 is rt varchar2(100):= p_under_score___; 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___comma___(p_comma___ varchar2) return varchar2 is rt varchar2(100):=p_comma___; begin return rt; end; 
    function of___dot___(p_dot___ varchar2) return varchar2 is rt varchar2(100) := dot___; begin return rt; end; 
    function of___minus___(p_minus___ varchar2) return varchar2 is rt varchar2(100) := minus___; begin return rt; end; 
end;
/

create or replace procedure proc___raise_exception___(p_expt_no in number)
as
	call_exception varchar2(100) := 'raise_application_error'
	||const___symbolic_character___.of___left_parentheses___
	||const___symbolic_character___.of___minus___
	||p_expt_no
	||const___symbolic_character___.of___comma___
	||'const___exception_usage___'
	||const___symbolic_character___.of___dot___
	||'of___expt_'
	|| p_expt_no
	||'_message___'
	||const___symbolic_character___.of___right_parentheses___
	||const___symbolic_character___.of___semi_coron___
	;
	exe_script varchar2(100) := '';
begin
	exe_script := const___symbolic_character___.of___begin___
	||const___symbolic_character___.of___space___
	||call_exception
	||const___symbolic_character___.of___space___
	||const___symbolic_character___.of___end___
	||const___symbolic_character___.of___semi_coron___
	;
	execute immediate exe_script;
end;
/

create or replace procedure proc___disp_err_msg___(p_srt_no in number,p_end_no in number)
as
	exe_script varchar2(100) := const___symbolic_character___.of___begin___
	||const___symbolic_character___.of___space___
	|| 'proc___raise_exception___'
	||const___symbolic_character___.of___left_parentheses___
	|| ':i'
	||const___symbolic_character___.of___right_parentheses___
	||const___symbolic_character___.of___semi_coron___
	||const___symbolic_character___.of___space___
	||const___symbolic_character___.of___end___
	||const___symbolic_character___.of___semi_coron___
	;
begin
	for i in 20000 + p_srt_no - 1 .. 20000 + p_end_no - 1 loop
		begin
			execute immediate exe_script using i;
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
end;
/


実行ログ

コード表示

exec proc___disp_err_msg___(1,5);
/

[ -20000]ORA-20000: exception_20000
[ -20001]ORA-20001: exception_20001
[ -20002]ORA-20002: exception_20002
[ -6550]ORA-06550: 行1、列65:
PLS-00302: コンポーネントOF___EXPT_20003_MESSAGE___を宣言してください。
[ -6550]ORA-06550: 行1、列65:
PLS-00302: コンポーネントOF___EXPT_20004_MESSAGE___を宣言してください。


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


exec proc___raise_exception___(20000);
/
exec proc___raise_exception___(20001);
/
exec proc___raise_exception___(20002);
/


次のコマンドの開始中にエラーが発生しました : 行 116 -
BEGIN proc___raise_exception___(20000); END;
エラー・レポート -
ORA-20000: exception_20000
ORA-06512: 行1
ORA-06512: "AINE.PROC___RAISE_EXCEPTION___", 行27
ORA-06512: 行1
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.

次のコマンドの開始中にエラーが発生しました : 行 118 -
BEGIN proc___raise_exception___(20001); END;
エラー・レポート -
ORA-20001: exception_20001
ORA-06512: 行1
ORA-06512: "AINE.PROC___RAISE_EXCEPTION___", 行27
ORA-06512: 行1


次のコマンドの開始中にエラーが発生しました : 行 120 -
BEGIN proc___raise_exception___(20002); END;
エラー・レポート -
ORA-20002: exception_20002
ORA-06512: 行1
ORA-06512: "AINE.PROC___RAISE_EXCEPTION___", 行27
ORA-06512: 行1


sleepとかの話

参考文献

ここに書いてあることを理解できるようになる。まだできていない。

DBMS_LOCK.SLEEPの代替SQLをいろいろ考えてみよう(Oracle)  

コード表示

declare
	SRT_TP timestamp;
	END_TP timestamp;
begin
	select SYSTIMESTAMP into SRT_TP from DUAL;
	DBMS_SESSION.SLEEP(1);
	select SYSTIMESTAMP into END_TP from DUAL;
	DBMS_OUTPUT.PUT_LINE(END_TP - SRT_TP);
	select SYSTIMESTAMP into SRT_TP from DUAL;
	DBMS_SESSION.SLEEP(5);
	select SYSTIMESTAMP into END_TP from DUAL;
	DBMS_OUTPUT.PUT_LINE(END_TP - SRT_TP);
end;
/

+000000000 00:00:00.999889000
+000000000 00:00:04.999971000


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

appendヒントとかbulk collectとかcollectionとかconnect byとかの話(nologgingパターンは忘れた)

はじめに

plsqlにdml文書いてあそんでいただけの話です。ほんとうにとりとめのない話です。

小ネタ

connect by level <= 10000000はoraerror。1000万件エラー。100万件はOK。connect byの限界(デフォルト)。ただメモリいじればいけると思う。ただマウントからやり直してインスタンス再起動はめんどい。結合で回避。

コード表示

create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 10000000
エラー・レポート -
ORA-30009: CONNECT BY操作のためのメモリーが足りません
30009. 0000 -  "Not enough memory for %s operation"
*Cause:    The memory size was not sufficient to process all the levels of the
           hierarchy specified by the query.
*Action:   In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to
           a reasonably larger value.
           Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a
           reasonably larger value.

select * from V$PARAMETER where NAME in ('workarea_size_policy','pga_aggregate_target');

drop table tbl___src___ purge;
--limit
create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 1000000;
--ORA-30009
create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 10000000;
--join
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;

オブジェクト型とコレクション型作る

クエリ

コード表示

create or replace type item is object (rn number,str varchar2(10));
/
create or replace type liz is table of item;
/

小ネタ

正規表現つかうとオシャンにいろいろできたりする。スクリプトをコールする前で前処理を組み込みたいとき、どうしたらいいかと思う。正規表現使ってハンディにできないかと考えたら、できるという話。

まず、コンパイルする。コンソールにはかれたプロシージャ名をコピってexecなんとかに書き換える。

コード表示

exec PROC___SELECT_INSERT___
exec PROC___DIRECT_PATH_INSERT___
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___

--置換前:$
--置換後:;\r\n/

exec PROC___SELECT_INSERT___;
/
exec PROC___DIRECT_PATH_INSERT___;
/
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/

必要な前処理が以下であるとする。

コード表示

drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

前処理を組み込むまえにonelinerにしておく。

コード表示

--置換前:$
--置換後:\\r\\n

drop table tbl___src___ purge;\r\n
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level &lt;= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;\r\n
drop table tbl___tar___ purge;\r\n
create table tbl___tar___ as select * from tbl___src___ where 1 &lt;&gt; 1;\r\n
alter system checkpoint;\r\n
--alter system switch logfile;\r\n
alter system flush shared_pool;\r\n
alter system flush buffer_cache;\r\n

--置換前:\r\n
--置換後:

drop table tbl___src___ purge;\r\ncreate table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level &lt;= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;\r\ndrop table tbl___tar___ purge;\r\ncreate table tbl___tar___ as select * from tbl___src___ where 1 &lt;&gt; 1;\r\nalter system checkpoint;\r\n--alter system switch logfile;\r\nalter system flush shared_pool;\r\nalter system flush buffer_cache;\r\n

onelinerにした後のスニペットをexecの前にぴろっと入れる。

コード表示

--置換前:^(?=exec)
--置換後:drop table tbl___src___ purge;\r\ncreate table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;\r\ndrop table tbl___tar___ purge;\r\ncreate table tbl___tar___ as select * from tbl___src___ where 1 <> 1;\r\nalter system checkpoint;\r\n--alter system switch logfile;\r\nalter system flush shared_pool;\r\nalter system flush buffer_cache;\r\n

drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___SELECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___DIRECT_PATH_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/


コンパイルスクリプト

まぁあそびです。

コード表示

create or replace procedure proc___select_insert___ as
	srt_tp timestamp;
	end_tp timestamp;
begin
	select systimestamp into srt_tp from dual;
	insert into tbl___tar___
		select
			*
		from
			tbl___src___;
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___select_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___direct_path_insert___ as
	srt_tp timestamp;
	end_tp timestamp;
begin
	select systimestamp into srt_tp from dual;
	insert /*+ append */ into tbl___tar___
		select
			*
		from
			tbl___src___;
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___direct_path_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch');
	dbms_output.put_line(end_tp - srt_tp);
	select systimestamp into srt_tp from dual;
	insert into tbl___tar___
		select
			*
		from
			table ( lz );
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch');
	dbms_output.put_line(end_tp - srt_tp);
	select systimestamp into srt_tp from dual;
	insert /*+ append */ into tbl___tar___
		select
			*
		from
			table ( lz );
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_bulk_collect_insert_no_limit___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_bulk_collect_insert_no_limit___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_bulk_collect_insert_no_limit___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_bulk_collect_insert_no_limit___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_bulk_collect_insert_limit_1000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 1000;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_bulk_collect_insert_limit_1000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_bulk_collect_insert_limit_1000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 1000;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_bulk_collect_insert_limit_1000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_bulk_collect_insert_limit_10000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 10000;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_bulk_collect_insert_limit_10000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_bulk_collect_insert_limit_10000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 10000;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_bulk_collect_insert_limit_10000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_no_bulk_collect_insert___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		cast(collect(item(rn, str) ) as liz)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr into lz;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_no_bulk_collect_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_no_bulk_collect_insert___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		cast(collect(item(rn, str) ) as liz)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr into lz;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_no_bulk_collect_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/

小ネタからできた実行スクリプト

コード表示

drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___SELECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___DIRECT_PATH_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/

実行結果コンソールログ

ここから必要な情報を抜き取る。そのままべと。

コード表示


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___select_insert___
+000000000 00:00:01.451000000


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


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___direct_path_insert___
+000000000 00:00:01.549663000


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


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.844085000
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:08.425256000


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


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.904504000
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:10.287960000


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


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.862762000


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


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.909035000


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


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_bulk_collect_insert_limit_1000___
+000000000 00:00:14.981688000


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


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_bulk_collect_insert_limit_1000___
+000000000 00:01:45.558595000


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


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:14.202969000


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


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:25.469245000


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


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_no_bulk_collect_insert___
+000000000 00:00:24.833968000


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


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_no_bulk_collect_insert___
+000000000 00:00:18.574835000


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

--置換前:^(?!.*(proc|\+)).*$
--置換後:

--置換前:^\r\n
--置換後:

proc___select_insert___
+000000000 00:00:01.451000000
proc___direct_path_insert___
+000000000 00:00:01.549663000
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.844085000
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:08.425256000
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.904504000
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:10.287960000
proc___no_append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.862762000
proc___append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.909035000
proc___no_append_hint_bulk_collect_insert_limit_1000___
+000000000 00:00:14.981688000
proc___append_hint_bulk_collect_insert_limit_1000___
+000000000 00:01:45.558595000
proc___no_append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:14.202969000
proc___append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:25.469245000
proc___no_append_hint_no_bulk_collect_insert___
+000000000 00:00:24.833968000
proc___append_hint_no_bulk_collect_insert___
+000000000 00:00:18.574835000


--置換前:\r\n
--置換後:\t

proc___select_insert___	+000000000 00:00:01.451000000	proc___direct_path_insert___	+000000000 00:00:01.549663000	proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.844085000	proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:08.425256000	proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.904504000	proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:10.287960000	proc___no_append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.862762000	proc___append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.909035000	proc___no_append_hint_bulk_collect_insert_limit_1000___	+000000000 00:00:14.981688000	proc___append_hint_bulk_collect_insert_limit_1000___	+000000000 00:01:45.558595000	proc___no_append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:14.202969000	proc___append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:25.469245000	proc___no_append_hint_no_bulk_collect_insert___	+000000000 00:00:24.833968000	proc___append_hint_no_bulk_collect_insert___	+000000000 00:00:18.574835000	


--置換前:proc
--置換後:\r\nproc


proc___select_insert___	+000000000 00:00:01.451000000	
proc___direct_path_insert___	+000000000 00:00:01.549663000	
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.844085000	
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:08.425256000	
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.904504000	
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:10.287960000	
proc___no_append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.862762000	
proc___append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.909035000	
proc___no_append_hint_bulk_collect_insert_limit_1000___	+000000000 00:00:14.981688000	
proc___append_hint_bulk_collect_insert_limit_1000___	+000000000 00:01:45.558595000	
proc___no_append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:14.202969000	
proc___append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:25.469245000	
proc___no_append_hint_no_bulk_collect_insert___	+000000000 00:00:24.833968000	
proc___append_hint_no_bulk_collect_insert___	+000000000 00:00:18.574835000	

測定結果

普通にcollectionとかでこねくりまわさんほうがよさげ。limit句で処理量を多くすると早くなるのはほぉーとなった。

おまけ

こういうのって追加パターンあるとメンテめんどってなるから、なるたけハンディにやりたい。そういった話。最初に思いついたやつ。

コード表示

declare
	cursor csr is
	with sub as(
	select 'PROC___SELECT_INSERT___'as proc from dual union all
	select 'PROC___DIRECT_PATH_INSERT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual union all
	select 'PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual
	)select * from sub;

proc clob;

begin
	open csr;
	loop
		begin
		fetch csr into proc;
		exit when csr%notfound;
		execute immediate 'begin '|| proc || '; end;';
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
	close csr;
end;
/

うえのだとあるスクリプトだけ実行したいってなったときコメントアウトするけど、アウトするところによってはコンパイルエラーとなってめんど。なので、実行スクリプト名を格納するテーブル適当につくる。こんな感じ。

コード表示

drop table exec_script purge;
create table exec_script as
	with sub as(
	select 'PROC___SELECT_INSERT___'as proc from dual union all
	select 'PROC___DIRECT_PATH_INSERT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual union all
	select 'PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual
	)select row_number() over (order by rownum) as seq,proc from sub;

select * from exec_script;

んで、cursorのところちょちょっとなおす。こんな感じ。where句に実行したいseqを指定できるようになるから、何番目以降とか指定できたり、特定の処理だけとかもできる。

コード表示

declare
	cursor csr is select proc from exec_script where seq = 1;
--	cursor csr is select proc from exec_script where seq >= 1;
--	cursor csr is select proc from exec_script where seq between 3 and 5;
	proc clob;
begin
	open csr;
	loop
		begin
		fetch csr into proc;
		exit when csr%notfound;
		execute immediate 'begin '|| proc || '; end;';
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
	close csr;
end;
/

前処理とかもテーブル入れておけばいいですかね。後処理とかも同様なノリでできるでしょう。

コード表示

drop table pre_script purge;
create table pre_script as
with sub as(
select 'drop table tbl___src___ purge' as proc from dual union all
select 'create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7' as proc from dual union all
select 'drop table tbl___tar___ purge' as proc from dual union all
select 'create table tbl___tar___ as select * from tbl___src___ where 1 <> 1' as proc from dual union all
select 'alter system checkpoint' as proc from dual union all
select '--alter system switch logfile' as proc from dual union all
select 'alter system flush shared_pool' as proc from dual union all
select 'alter system flush buffer_cache' as proc from dual
)select row_number() over (order by rownum) as seq,proc from sub;
select * from pre_script;

まぁ、なんでこの話したかっていうと、merge文のパターン忘れたなとおもったからで、実行するプロシージャと処理するプロシージャは切り離しておけばいいかなーとめんどくならないかなーとおもっただけのことです。実行するプロシージャもオブジェクトにしておけば実行もそんなにかかなくていいですね。こんな感じ。

コード表示

create or replace procedure script_run(p_pre_from_seq number:=1,p_pre_to_seq number:=1,p_exec_from_seq number:=1,p_exec_to_seq number:=1)
authid current_user
as
	cursor pre_csr is select proc from pre_script where seq between p_pre_from_seq and p_pre_to_seq;
	cursor exec_csr is select proc from exec_script where seq between p_exec_from_seq and p_exec_to_seq;
	pre_proc clob;
	exec_proc clob;
begin
	open exec_csr;
	loop
		begin
		fetch exec_csr into exec_proc;
		exit when exec_csr%notfound;
			open pre_csr;
			loop
				begin
				fetch pre_csr into pre_proc;
				exit when pre_csr%notfound;
				dbms_output.put_line(pre_proc);
				execute immediate pre_proc;
				exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
				end;
			end loop;
			close pre_csr;
		dbms_output.put_line('begin '|| exec_proc || '; end;');
		execute immediate 'begin '|| exec_proc || '; end;';
		dbms_output.put_line(lpad('_',60,'_'));
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
	close exec_csr;
end;
/

ついでに忘れた、merge文のパターンも

コード表示

create or replace procedure proc___merge_insert___ as
	srt_tp timestamp;
	end_tp timestamp;
begin
	select systimestamp into srt_tp from dual;
	merge into tbl___tar___ tar
	using(select * from tbl___src___) src
	on(tar.rn = src.rn and tar.str = src.str)
	when not matched then insert values(src.rn,src.str);
    commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___merge_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/

うえでつくったプロシージャ名をテーブルに投入。

コード表示

select * from exec_script;

merge into exec_script tar
using (
	with sub as(
	select 'PROC___MERGE_INSERT___' as proc from dual
	)select max(s2.seq) + 1 as seq,max(s1.proc) as proc from sub s1,exec_script s2
)src
on(tar.proc = src.proc)
when not matched then insert values(src.seq,src.proc);
commit;

select * from exec_script;

実行例。コメントとしてテーブル格納しておけば、無視してくれてるポイから、いけてる雰囲気。

コード表示

set serveroutput on;
exec script_run(1,8,1,2);
/
drop table tbl___src___ purge
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7
drop table tbl___tar___ purge
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1
alter system checkpoint
--alter system switch logfile
[ -900]ORA-00900: SQL文が無効です。
alter system flush shared_pool
alter system flush buffer_cache
begin PROC___SELECT_INSERT___; end;
proc___select_insert___
+000000000 00:00:01.351316000
____________________________________________________________
drop table tbl___src___ purge
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7
drop table tbl___tar___ purge
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1
alter system checkpoint
--alter system switch logfile
[ -900]ORA-00900: SQL文が無効です。
alter system flush shared_pool
alter system flush buffer_cache
begin PROC___DIRECT_PATH_INSERT___; end;
proc___direct_path_insert___
+000000000 00:00:01.449993000
____________________________________________________________


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


おわりに

plsqlたのしいな!もっと楽しい方法あったら、教えてください。以上、ありがとうございました。

plsql___変数名ルール___

いろいろ流派はあると思うけど、アンスコは3つ(勘)。無名ブロックで使用する変数は自由でいいと思う。無名ブロック以外のストアドの引数や戻り値とかは接頭辞ルール守る。無名ブロック以外の引数や戻り値を除けば他は自由でOK。

:=の両端と演算子の前後はスペース1つあける。登場するスペースはすべて半角で。パブリックやプライベートは変数名に現れなくてもいい気がする。最初のうちは。なんかうっとうしくなりそう。気にせずいこう。

using句のバインド変数はexecute immediateで実行するファンクション、プロシージャ、SQL文に:variableをハードで埋め込む必要があるのを忘れない。コロン忘れそう。

接頭辞は迷っているんだよなー。オブジェクト型ビューで管理されているから、接頭辞にせず、つけたらいいかなって最近思い始めた。

プログラミングでよく使う英単語のまとめ【随時更新】  
英語で数字の単位が読み方まで一覧でわかる!音声付きまとめ  

命名対象 オブジェクト 変数名
定数 パッケージ const___variable___
定数 ファンクション of___variable___
ファンクション名(判定系) ファンクション is___variable___
ファンクション名(変換系) ファンクション cnv___variable_to_variable___
ファンクション名(算術系) ファンクション clc___variable___
戻り値 ファンクション rt
戻り値 プロシージャ r_variable
引数 ファンクション p_variable
引数 プロシージャ p_variable
パッケージ名(定数以外) パッケージ pkg___variable___
プロシージャ名 プロシージャ proc___variable___
ファンクション名(定数以外) ファンクション func___variable___
タイプ名 タイプ typ___variable___
テーブル名 テーブル tbl___variable___
サブクエリ名 サブクエリ sub___variable___

動的にプロシージャを実行する話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

テンプレ

動的にプロシージャcallできた

コード表示

create or replace procedure proc___01___(p_num in number,r_val out number,r_proc_name out varchar2)
as
begin
	r_val:=p_num * 10;
	r_proc_name:='proc___01___';
	return;	
end;
/
create or replace procedure proc___02___(p_num in number,r_val out number,r_proc_name out varchar2)
as
begin
	r_val:=p_num * 100;
	r_proc_name:='proc___02___';
	return;	
end;
/

set serveroutput on;
declare
rt number;
proc_name varchar2(100);
begin
	proc___01___(10,rt,proc_name);
	dbms_output.put_line(proc_name || ' is called');
	dbms_output.put_line(rt);
end;
/

declare
rt number;
proc_name varchar2(100);
begin
	proc___02___(10,rt,proc_name);
	dbms_output.put_line(proc_name || ' is called');
	dbms_output.put_line(rt);
end;
/

create or replace package const___reserved_words___
as
	begin___ constant varchar2(100):='begin ';
	end___ constant varchar2(100):=' end';
	space___ constant varchar2(100):=' ';
	semi_coron___ constant varchar2(100):=';';
	prefix_procedure___ constant varchar2(100):='proc';
	under_score___ constant varchar2(100):='___';
	left_parentheses___ constant varchar2(100):='(';
	right_parentheses___ constant varchar2(100):=')';
	comma___ constant varchar2(100):=',';
	return_val___ constant varchar2(100):='rt';
	procedure_name___ constant varchar2(100):='proc_name';
	varchar2___ constant varchar2(100):='varchar2';
	varchar2___4000_bytes___ constant varchar2(100):='4000';
	coron___ constant varchar2(100):=':';
	single_quote___ constant varchar2(100):='''';
	equal___ constant varchar2(100):='=';
	declare___ constant varchar2(100):='declare';

    function of___begin___(p_begin___ varchar2:=begin___) return varchar2;
    function of___end___(p_end___ varchar2:=end___) return varchar2;
    function of___space___(p_space___ varchar2:=space___) return varchar2;
    function of___semi_coron___(p_semi_coron___ varchar2:=semi_coron___) return varchar2;
    function of___prefix_procedure___(p_prefix_procedure___ varchar2:=prefix_procedure___) return varchar2;
    function of___under_score___(p_under_score___ varchar2:=under_score___) 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___comma___(p_comma___ varchar2:=comma___) return varchar2;
    function of___return_val___(p_return_val___ varchar2:=return_val___) return varchar2;
    function of___procedure_name___(p_procedure_name___ varchar2:=procedure_name___) return varchar2;
    function of___varchar2___(p_varchar2___ varchar2:=varchar2___) return varchar2;
    function of___varchar2___4000_bytes___(p_varchar2___4000_bytes___ varchar2:=varchar2___4000_bytes___) return varchar2;
    function of___coron___(p_coron___ varchar2:=coron___) return varchar2;
    function of___single_quote___(p_single_quote___ varchar2:=single_quote___) return varchar2;
    function of___equal___(p_equal___ varchar2:=equal___) return varchar2;
    function of___declare___(p_declare___ varchar2:=declare___) return varchar2;
end;
/

create or replace package body const___reserved_words___
as
    function of___begin___(p_begin___ varchar2) return varchar2 is rt varchar2(100):=p_begin___; begin return rt; end; 
    function of___end___(p_end___ varchar2) return varchar2 is rt varchar2(100):=p_end___; begin return rt; end; 
    function of___space___(p_space___ varchar2) return varchar2 is rt varchar2(100):=p_space___; 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___prefix_procedure___(p_prefix_procedure___ varchar2) return varchar2 is rt varchar2(100):=p_prefix_procedure___; begin return rt; end; 
    function of___under_score___(p_under_score___ varchar2) return varchar2 is rt varchar2(100):=p_under_score___; 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___comma___(p_comma___ varchar2) return varchar2 is rt varchar2(100):=p_comma___; begin return rt; end; 
    function of___return_val___(p_return_val___ varchar2) return varchar2 is rt varchar2(100):=p_return_val___; begin return rt; end; 
    function of___procedure_name___(p_procedure_name___ varchar2) return varchar2 is rt varchar2(100):=p_procedure_name___; begin return rt; end; 
    function of___varchar2___(p_varchar2___ varchar2) return varchar2 is rt varchar2(100):=p_varchar2___; begin return rt; end; 
    function of___varchar2___4000_bytes___(p_varchar2___4000_bytes___ varchar2) return varchar2 is rt varchar2(100):=p_varchar2___4000_bytes___; begin return rt; end; 
    function of___coron___(p_coron___ varchar2) return varchar2 is rt varchar2(100):=p_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___equal___(p_equal___ varchar2) return varchar2 is rt varchar2(100):=p_equal___; begin return rt; end; 
    function of___declare___(p_declare___ varchar2) return varchar2 is rt varchar2(100):=p_declare___; begin return rt; end; 
end;
/

create or replace procedure proc___invocker___(p_proc_type in number,p_num in number)
is
	build_proc varchar2(4000):='';
	rt varchar2(4000):='';
	proc_name varchar2(4000):='';
begin
	build_proc:=
	const___reserved_words___.of___declare___
	|| const___reserved_words___.of___space___
	|| const___reserved_words___.of___return_val___
	|| const___reserved_words___.of___space___
	|| const___reserved_words___.of___varchar2___ 
	|| const___reserved_words___.of___left_parentheses___
	|| const___reserved_words___.of___varchar2___4000_bytes___ 
	|| const___reserved_words___.of___right_parentheses___
	|| const___reserved_words___.of___coron___ 
	|| const___reserved_words___.of___equal___
	|| const___reserved_words___.of___single_quote___
	|| const___reserved_words___.of___single_quote___
	|| const___reserved_words___.of___semi_coron___
	|| const___reserved_words___.of___space___
	|| const___reserved_words___.of___procedure_name___
	|| const___reserved_words___.of___space___
	|| const___reserved_words___.of___varchar2___ 
	|| const___reserved_words___.of___left_parentheses___
	|| const___reserved_words___.of___varchar2___4000_bytes___ 
	|| const___reserved_words___.of___right_parentheses___
	|| const___reserved_words___.of___coron___ 
	|| const___reserved_words___.of___equal___
	|| const___reserved_words___.of___single_quote___
	|| const___reserved_words___.of___single_quote___
	|| const___reserved_words___.of___semi_coron___
	|| const___reserved_words___.of___space___
	|| const___reserved_words___.of___begin___
	|| const___reserved_words___.of___prefix_procedure___
	|| const___reserved_words___.of___under_score___
	|| lpad(p_proc_type,2,0)
	|| const___reserved_words___.of___under_score___
	|| const___reserved_words___.of___left_parentheses___
	|| p_num
	|| const___reserved_words___.of___comma___
	|| const___reserved_words___.of___coron___ 
	|| const___reserved_words___.of___return_val___
	|| const___reserved_words___.of___comma___
	|| const___reserved_words___.of___coron___ 
	|| const___reserved_words___.of___procedure_name___
	|| const___reserved_words___.of___right_parentheses___
	|| const___reserved_words___.of___semi_coron___
	|| const___reserved_words___.of___end___
	|| const___reserved_words___.of___semi_coron___;
	dbms_output.put_line(build_proc);
	execute immediate build_proc using out rt,out proc_name;
	dbms_output.put_line(proc_name || ' is called');
	dbms_output.put_line(rt);
	return;	
end;
/

set serveroutput on;
exec proc___invocker___(1,10);
/
exec proc___invocker___(2,10);
/



declare
	mx number;
begin
	select count(*) into mx from dba_objects where object_type = 'PROCEDURE' and object_name like 'PROC' || '%' and object_name not like '%' || 'INVOCKER'  || '%';
	for i in 1..mx loop
		execute immediate 'begin proc___invocker___('|| i || ',10); end;';	
	end loop;
end;
/

コンソール出力内容

実行結果

コード表示

declare rt varchar2(4000):=''; proc_name varchar2(4000):=''; begin proc___01___(10,:rt,:proc_name); end;
proc___01___ is called
100
declare rt varchar2(4000):=''; proc_name varchar2(4000):=''; begin proc___02___(10,:rt,:proc_name); end;
proc___02___ is called
1000


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

スニペット

sqldeveloperに設定するやつ。このいちいち登録するのがいや面倒。いい方法ないかな。

コード表示

c_bg	const___reserved_words___.of___begin___
c_cm	const___reserved_words___.of___comma___
c_cn	const___reserved_words___.of___coron___ 
c_dc	const___reserved_words___.of___declare___
c_ed	const___reserved_words___.of___end___
c_eq	const___reserved_words___.of___equal___
c_lp	const___reserved_words___.of___left_parentheses___
c_pn	const___reserved_words___.of___procedure_name___
c_pp	const___reserved_words___.of___prefix_procedure___
c_rp	const___reserved_words___.of___right_parentheses___
c_rt	const___reserved_words___.of___return_val___
c_sc	const___reserved_words___.of___semi_coron___
c_sp	const___reserved_words___.of___space___
c_sq	const___reserved_words___.of___single_quote___
c_un	const___reserved_words___.of___under_score___
c_v2	const___reserved_words___.of___varchar2___ 
c_v4tb	const___reserved_words___.of___varchar2_4000_bytes___ 

動的にファンクションの呼び出しを制御する話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

ファンクション2つ適当に作る

クエリ

コード表示

create or replace function func___01___(p_num number)
return number
as rt number := p_num;
begin
	return rt * 10;
end;
/

create or replace function func___02___(p_num number)
return number
as rt number := p_num;
begin
	return rt * 100;
end;
/

select FUNC___01___(10) as rt from dual;
select FUNC___02___(10) as rt from dual;

実行例

コード表示

AINE@pdb1> select FUNC___01___(10) as rt from dual;

           RT
-------------
          100

1 row selected.

Elapsed: 00:00:00.00

AINE@pdb1> select FUNC___02___(10) as rt from dual;

           RT
-------------
         1000

1 row selected.

Elapsed: 00:00:00.00

ファンクション呼び出しファンクション

クエリ

コード表示


create or replace function func___invocker___(p_func_type number,p_num number)
return number
as rt number;
build_sql clob := to_clob('');
begin
	build_sql := 'select '||'func'||'___' || lpad(p_func_type,2,0) || '___' || '(' ||':p_num'|| ')' || ' from dual';
	execute IMMEDIATE build_sql into rt using p_num;
	return rt;
end;
/

実行例

コード表示

AINE@pdb1> select func___invocker___(1,10) as rt from dual;

           RT
-------------
          100

1 row selected.

Elapsed: 00:00:00.00
AINE@pdb1> select func___invocker___(2,10) as rt from dual;

           RT
-------------
         1000

1 row selected.

Elapsed: 00:00:00.01

パッケージにくるんでみると、パッケージ内での処理を気にせず、呼び出し側はcallできる。

クエリ

コード表示

create or replace package func___pkg___ is
	function func___01___(p_num number) return number;
	function func___02___(p_num number) return number;
end;
/
create or replace package body func___pkg___ is
	procedure func___fmt___(mtd varchar2) as
	begin
		DBMS_OUTPUT.PUT_LINE(mtd||'is called');
		return;
	end;
	function func___01___(p_num number)
	return number
	as rt number := p_num;
	begin
		func___fmt___('func___01___');
		return rt * 10;
	end;
	function func___02___(p_num number)
	return number
	as rt number := p_num;
	begin
		func___fmt___('func___02___');
		return rt * 100;
	end;
end;
/
select FUNC___PKG___.FUNC___01___(10) as rt from dual;
select FUNC___PKG___.FUNC___02___(10) as rt from dual;

実行例

コード表示

AINE@pdb1> select FUNC___PKG___.FUNC___01___(10) as rt from dual;

           RT
-------------
          100

1 row selected.

func___01___is called
Elapsed: 00:00:00.00

AINE@pdb1> select FUNC___PKG___.FUNC___02___(10) as rt from dual;

           RT
-------------
         1000

1 row selected.

func___02___is called
Elapsed: 00:00:00.00

パッケージの文言を追加しただけ。

クエリ

コード表示

create or replace function func___invocker___(p_func_type number,p_num number)
return number
as rt number;
build_sql clob := to_clob('');
begin
	build_sql := 'select '|| 'func___pkg___.' ||'func'||'___' || lpad(p_func_type,2,0) || '___' || '(' ||':p_num'|| ')' || ' from dual';
	execute IMMEDIATE build_sql into rt using p_num;
	return rt;
end;
/
select func___invocker___(1,10) as rt from dual;
select func___invocker___(2,10) as rt from dual;

実行例

コード表示

AINE@pdb1> select func___invocker___(1,10) as rt from dual;

           RT
-------------
          100

1 row selected.

func___01___is called
Elapsed: 00:00:00.00
AINE@pdb1> select func___invocker___(2,10) as rt from dual;

           RT
-------------
         1000

1 row selected.

func___02___is called
Elapsed: 00:00:00.00

collect関数でmapが作成できた話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

タイプ作る

2つ今回は。

コード表示

drop type mp force;
/
drop type liz force;
/

create or replace type mp is object(ky clob,vl clob);
/
create or replace type liz is table of mp;
/


はじめのデータ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7
)select * from sub;

実行例

コード表示

AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7 )select * from sub;

           RN KY           VL
------------- ------------ ----
            0 001          A
            0 002          B
            0 003          C
            1 001          A
            1 002          B
            1 003          C
            1 004          D

7 rows selected.

Elapsed: 00:00:00.00

マップオブジェクトできた

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7
)select rn,mp(ky,vl) as mp from sub;

実行例

コード表示

AINE@pdb1> col mp for a100
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7 )select rn,mp(ky,vl) as mp from sub;

           RN MP(KY, VL)
------------- ----------------------------------------------------------------------------------------------------
            0 MP('001', 'A')
            0 MP('002', 'B')
            0 MP('003', 'C')
            1 MP('001', 'A')
            1 MP('002', 'B')
            1 MP('003', 'C')
            1 MP('004', 'D')

7 rows selected.

Elapsed: 00:00:00.01

さまっておしまい

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7
)select rn,cast(collect(mp(ky,vl)) as liz) as liz from sub group by rn;

実行例

コード表示

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as ky,chr(64+row_number()over(partition by mod(level,2) order by level)) as vl from dual connect by level <= 7 )select rn,cast(collect(mp(ky,vl)) as liz) as liz from sub group by rn;

           RN LIZ(KY, VL)
------------- ----------------------------------------------------------------------------------------------------
            0 LIZ(MP('001', 'A'), MP('003', 'C'), MP('002', 'B'))
            1 LIZ(MP('001', 'A'), MP('004', 'D'), MP('003', 'C'), MP('002', 'B'))

2 rows selected.

Elapsed: 00:00:00.00

collect関数で入れ子できた話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

タイプ作る

3つ今回は。

コード表示

drop type val force;
/
drop type item force;
/
drop type liz force;
/

create or replace type val is object(val clob);
/
create or replace type item is object(rn number,vl val);
/
create or replace type liz is table of item;
/

はじめのデータ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select * from sub;

実行例

コード表示

AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select * from sub;

           RN STR
------------- ------------
            0 001
            0 002
            0 003
            1 001
            1 002
            1 003
            1 004

7 rows selected.

Elapsed: 00:00:00.00

strをオブジェクト型に変換

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select rn,val(str) as val from sub;

実行例

コード表示

AINE@pdb1> col val for a50
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select rn,val(str) as val from sub;

           RN VAL(VAL)
------------- --------------------------------------------------
            0 VAL('001')
            0 VAL('002')
            0 VAL('003')
            1 VAL('001')
            1 VAL('002')
            1 VAL('003')
            1 VAL('004')

7 rows selected.

Elapsed: 00:00:00.01

mapみたいにitemでラップ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select item(rn,val(str)) as item from sub;

実行例

コード表示

AINE@pdb1> col item for a50
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select item(rn,val(str)) as item from sub;

ITEM(RN, VL(VAL))
--------------------------------------------------
ITEM(0, VAL('001'))
ITEM(0, VAL('002'))
ITEM(0, VAL('003'))
ITEM(1, VAL('001'))
ITEM(1, VAL('002'))
ITEM(1, VAL('003'))
ITEM(1, VAL('004'))

7 rows selected.

Elapsed: 00:00:00.01

最後はさまっておしまい

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select rn,cast(collect(item(rn,val(str))) as liz) as liz from sub group by rn;

実行例

コード表示

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select rn,cast(collect(item(rn,val(str))) as liz) as liz from sub group by rn;

           RN LIZ(RN, VL(VAL))
------------- ----------------------------------------------------------------------------------------------------
            0 LIZ(ITEM(0, VAL('001')), ITEM(0, VAL('003')), ITEM(0, VAL('002')))
            1 LIZ(ITEM(1, VAL('001')), ITEM(1, VAL('004')), ITEM(1, VAL('003')), ITEM(1, VAL('002')))

2 rows selected.

Elapsed: 00:00:00.01

サマル単位を指定しないと

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select cast(collect(item(rn,val(str))) as liz)  as liz from sub;

実行例

コード表示

AINE@pdb1> col liz for a170
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select cast(collect(item(rn,val(str))) as liz) as liz from sub;

LIZ(RN, VL(VAL))
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM(0, VAL('001')), ITEM(0, VAL('002')), ITEM(0, VAL('003')), ITEM(1, VAL('001')), ITEM(1, VAL('002')), ITEM(1, VAL('003')), ITEM(1, VAL('004')))

1 row selected.

Elapsed: 00:00:00.01