Oracleraise_application_error错误号最佳实践 [英] Oracle raise_application_error error number best practice
问题描述
我对提高应用程序错误"的错误代码(-20000至-20999)有疑问.
I have a question regarding the error codes (-20000 to -20999) for Raise Application Error.
-
我们可以仅在PLSQL代码中的多个位置针对不同的错误场景使用相同的错误代码(例如-20000)吗?
Can we use same error code (eg -20000) only for different error scenarios at multiple places in PLSQL code?
如果我们可以在所有地方使用相同的错误代码，为什么我们有1000个代码?
If we can use same error code in all places, why do we have 1000 codes?
在引发应用程序错误"中使用错误代码的最佳实践是什么?
What is the best practice to use error codes in Raise Application Error?
示例代码:
create table t(id number primary key);
declare
begin
insert into t(id) values (1);
insert into t(id) values (1);
commit;
exception
when dup_val_on_index
then
raise_application_error(-20000, 'Cannot Insert duplicates');
when others
then
raise_application_error(-20000, sqlcode||'-'||sqlerrm);
end;
推荐答案
- 我们能否仅在PLSQL代码中的多个位置针对不同的错误场景使用相同的错误代码(例如-20000)?
正如Justin所指出的，您当然可以做到这一点-仅使用一个代码.但这很可能导致混乱.我已经看到了，通常，在那种情况下，开发人员只需将所有关键信息嵌入消息中，甚至包括代码(例如，他们可能已经在使用自己的错误代码，而这些错误代码超出了可接受的范围).
As Justin notes, you can certainly do that - just use one code. But it is likely to lead to confusion. I've seen it done, and usually in that case, the developers simply embed all critical information into the message, even including a code (they might, for example, already be using their own error codes that fall outside the acceptable range).
我建议您遵循Oracle的领导:将范围分配给应用程序的区域，然后在该部分的该部分中发生特定于应用程序的错误时，使用该范围内的错误代码.
I suggest you follow Oracle's lead: assign ranges to areas of your application and then use error codes within a range when an application-specific error occurs in that part of the part.
- 如果我们可以在所有地方使用相同的错误代码，为什么我们有1000个代码?
请参阅上文.
- 在引发应用程序错误"中使用错误代码的最佳实践是什么?
创建一个表，在该表中注册"所使用的错误代码以及该消息.然后，开发人员可以检查他们的"错误是否已注册并可以重新使用.或者，更有可能的是，他们注册了新的错误代码和消息.
Create a table in which you "register" error codes that are used, along with the message. Then developers can check to see "their" error is already registered and can re-use it. Or, more likely, they register a new error code and message.
无论哪种方式，您都有一个中心点来组织代码，并希望最小化使用相同错误代码的两个开发人员的更改.
Either way, you have a central point from which to organize the codes and hopefully minimize the change of two developers using the same error code.
这是一个脚本，可完成我上面的建议，还有一个实用程序，用于生成一个包，其中定义了所有错误，并且可供软编码"参考使用.
Here's a script that does what I suggested above, along with a utility to generate a package with all of the errors defined and available for "soft-coded" reference.
CREATE TABLE msg_info (
msgcode INTEGER,
msgtype VARCHAR2(30),
msgtext VARCHAR2(2000),
msgname VARCHAR2(30),
description VARCHAR2(2000)
);
CREATE OR REPLACE PACKAGE msginfo
IS
FUNCTION text (
code_in IN INTEGER
, type_in IN VARCHAR2
, use_sqlerrm IN BOOLEAN := TRUE
)
RETURN VARCHAR2;
FUNCTION name (code_in IN INTEGER, type_in IN VARCHAR2)
RETURN VARCHAR2;
PROCEDURE genpkg (
NAME_IN IN VARCHAR2
, oradev_use IN BOOLEAN := FALSE
, to_file_in IN BOOLEAN := TRUE
, dir_in IN VARCHAR2 := 'DEMO' -- UTL_FILE directory
, ext_in IN VARCHAR2 := 'pkg'
);
END;
/
CREATE OR REPLACE PACKAGE BODY msginfo
IS
FUNCTION msgrow (code_in IN INTEGER, type_in IN VARCHAR2)
RETURN msg_info%ROWTYPE
IS
CURSOR msg_cur
IS
SELECT *
FROM msg_info
WHERE msgtype = type_in AND msgcode = code_in;
msg_rec msg_info%ROWTYPE;
BEGIN
OPEN msg_cur;
FETCH msg_cur INTO msg_rec;
CLOSE msg_cur;
RETURN msg_rec;
END;
FUNCTION text (
code_in IN INTEGER
, type_in IN VARCHAR2
, use_sqlerrm IN BOOLEAN := TRUE
)
RETURN VARCHAR2
IS
msg_rec msg_info%ROWTYPE := msgrow (code_in, type_in);
BEGIN
IF msg_rec.msgtext IS NULL AND use_sqlerrm
THEN
msg_rec.msgtext := SQLERRM (code_in);
END IF;
RETURN msg_rec.msgtext;
END;
FUNCTION NAME (code_in IN INTEGER, type_in IN VARCHAR2)
RETURN VARCHAR2
IS
msg_rec msg_info%ROWTYPE := msgrow (code_in, type_in);
BEGIN
RETURN msg_rec.msgname;
END;
PROCEDURE genpkg (
NAME_IN IN VARCHAR2
, oradev_use IN BOOLEAN := FALSE
, to_file_in IN BOOLEAN := TRUE
, dir_in IN VARCHAR2 := 'DEMO'
, ext_in IN VARCHAR2 := 'pkg'
)
IS
CURSOR exc_20000
IS
SELECT *
FROM msg_info
WHERE msgcode BETWEEN -20999 AND -20000 AND msgtype = 'EXCEPTION';
-- Send output to file or screen?
v_to_screen BOOLEAN := NVL (NOT to_file_in, TRUE);
v_file VARCHAR2 (1000) := name_in || '.' || ext_in;
-- Array of output for package
TYPE lines_t IS TABLE OF VARCHAR2 (1000)
INDEX BY BINARY_INTEGER;
output lines_t;
-- Now pl simply writes to the array.
PROCEDURE pl (str IN VARCHAR2)
IS
BEGIN
output (NVL (output.LAST, 0) + 1) := str;
END;
-- Dump to screen or file.
PROCEDURE dump_output
IS
BEGIN
IF v_to_screen
THEN
FOR indx IN output.FIRST .. output.LAST
LOOP
DBMS_OUTPUT.put_line (output (indx));
END LOOP;
ELSE
-- Send output to the specified file.
DECLARE
fid UTL_FILE.file_type;
BEGIN
fid := UTL_FILE.fopen (dir_in, v_file, 'W');
FOR indx IN output.FIRST .. output.LAST
LOOP
UTL_FILE.put_line (fid, output (indx));
END LOOP;
UTL_FILE.fclose (fid);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'Failure to write output to '
|| dir_in
|| '/'
|| v_file
);
UTL_FILE.fclose (fid);
END;
END IF;
END dump_output;
BEGIN
/* Simple generator, based on DBMS_OUTPUT. */
pl ('CREATE OR REPLACE PACKAGE ' || NAME_IN);
pl ('IS ');
FOR msg_rec IN exc_20000
LOOP
IF exc_20000%ROWCOUNT > 1
THEN
pl (' ');
END IF;
pl (' exc_' || msg_rec.msgname || ' EXCEPTION;');
pl ( ' en_'
|| msg_rec.msgname
|| ' CONSTANT INTEGER := '
|| msg_rec.msgcode
|| ';'
);
pl ( ' PRAGMA EXCEPTION_INIT (exc_'
|| msg_rec.msgname
|| ', '
|| msg_rec.msgcode
|| ');'
);
IF oradev_use
THEN
pl (' FUNCTION ' || msg_rec.msgname || ' RETURN INTEGER;');
END IF;
END LOOP;
pl ('END ' || NAME_IN || ';');
pl ('/');
IF oradev_use
THEN
pl ('CREATE OR REPLACE PACKAGE BODY ' || NAME_IN);
pl ('IS ');
FOR msg_rec IN exc_20000
LOOP
pl (' FUNCTION ' || msg_rec.msgname || ' RETURN INTEGER');
pl (' IS BEGIN RETURN en_' || msg_rec.msgname || '; END;');
pl (' ');
END LOOP;
pl ('END ' || NAME_IN || ';');
pl ('/');
END IF;
dump_output;
END;
END;
/
/* Sample data to be used in package generation. */
BEGIN
INSERT INTO msg_info
VALUES (-20100, 'EXCEPTION', 'Balance too low', 'bal_too_low'
, 'Description');
INSERT INTO msg_info
VALUES (-20200, 'EXCEPTION', 'Employee too young', 'emp_too_young'
, 'Description');
COMMIT;
END;
/
这篇关于Oracleraise_application_error错误号最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
