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

エラーメッセージの実装について 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


Leave a Reply

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