如何记录 Oracle 包中的所有异常？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9184865/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to log all exceptions in Oracle package?
提问by Mike Christensen
I'm trying to log all exceptions in an Oracle package. Here's what I have at the end of the procedure:
我正在尝试在 Oracle 包中记录所有异常。这是我在程序结束时的内容：
EXCEPTION
WHEN OTHERS THEN
INSERT INTO VSLogger (MESSAGE) VALUES ('Caught Exception');
This works fine, however I also want to log the error code and message. I've tried:
这工作正常，但是我也想记录错误代码和消息。我试过了：
EXCEPTION
WHEN OTHERS THEN
INSERT INTO VSLogger (MESSAGE) VALUES ('Caught Exception: Error ' || SQLCODE || ', Msg: ' || SQLERRM);
But this gives me the error:
但这给了我错误：
490/7 PL/SQL: SQL Statement ignored
490/100 PL/SQL: ORA-00984: column not allowed here
What's the correct way to do this? Thanks!
这样做的正确方法是什么？谢谢！
采纳答案by Gaius
You can't use SQLERRMdirectly - you have to assign it to an intermediate variable. Note that Oracle 9i would let you get away with it, but that has always been the documented behavior. See herefor some sample code.
您不能SQLERRM直接使用- 您必须将其分配给一个中间变量。请注意，Oracle 9i 会让您逍遥法外，但这一直是记录在案的行为。有关一些示例代码，请参见此处。
You could also consider wrapping this bit in an autonomous transaction, so it gets logged even if your PL/SQL code's transaction gets rolled back.
您还可以考虑将这一位包装在一个自治事务中，这样即使您的 PL/SQL 代码的事务被回滚，它也会被记录下来。
回答by Jon Heller
Never use SQLERRMor SQLCODE. Exception logging without storing the line number is cruel.
永远不要使用SQLERRM或SQLCODE。不存储行号的异常记录是残酷的。
Always use dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, or something similar.
始终使用dbms_utility.format_error_stack||dbms_utility.format_error_backtrace，或类似的东西。
For example, the following block shows the error but not where it happened:
例如，以下块显示错误，但不显示发生位置：
declare
v_number number;
begin
v_number := 1/0;
exception when others then
dbms_output.put_line(sqlerrm);
end;
/
DBMS Output:
ORA-01476: divisor is equal to zero
This block shows both the error and where it happened. This is critical information for troubleshooting any non-trivial program.
此块显示错误及其发生的位置。这是对任何重要程序进行故障排除的关键信息。
declare
v_number number;
begin
v_number := 1/0;
exception when others then
dbms_output.put_line(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
end;
/
DBMS Output:
ORA-01476: divisor is equal to zero
ORA-06512: at line 4
(Below is some generic exception handling advice.)
（下面是一些通用的异常处理建议。）
For real code you would notwant to simply output the error information. In fact, the best exception handling strategy is usually to do nothing, and use Oracle's default behavior to display all the error messages and line numbers.
对于真正的代码，你会不会想简单输出错误信息。事实上，最好的异常处理策略通常是什么都不做，并使用 Oracle 的默认行为来显示所有错误消息和行号。
Custom exception handling in Oracle is generally only useful in one of these three cases:
Oracle 中的自定义异常处理通常仅在以下三种情况之一有用：
- You are going to do something with a specificerror, like call another procedure of just ignore the error.
- If this is a database-only program, then you might want to catch and log exceptions at the topof the program, at the (limited number) of entry points. You don't need exception handling if the application already catches everything, or for inner code. Exceptions propagate up the stack, it's best to catch them at the end.
- If you need to log some specific value that only exists as a local variable, it has to be logged immediately, since that value won't propagate with the exception.
- 您将针对特定错误执行某些操作，例如调用另一个忽略错误的过程。
- 如果这是一个仅限数据库的程序，那么您可能希望在程序顶部、（数量有限的）入口点处捕获和记录异常。如果应用程序已经捕获所有内容或内部代码，则不需要异常处理。异常沿堆栈向上传播，最好在最后捕获它们。
- 如果您需要记录一些仅作为局部变量存在的特定值，则必须立即记录，因为该值不会随异常一起传播。
There's a lot of PL/SQL code that blindly catches and logs errors in every procedure. That is unnecessary and usually counter-productive.
有很多 PL/SQL 代码盲目地捕获和记录每个过程中的错误。这是不必要的，通常会适得其反。
回答by Mike McAllister
Gaius gave you the short answer. His comment about wrapping it into an autonomous transaction is very important. You'll rue the day your transaction got rolled back, and you don't know why.
盖乌斯给了你简短的回答。他关于将其包装成自治事务的评论非常重要。你会后悔你的事务被回滚的那一天，你不知道为什么。
Here's something showing using an autonomous transaction with a few extra details thrown in so you know a whole bunch more about where your error occurred.
这里展示了使用自治事务并加入了一些额外的细节，这样您就可以更多地了解错误发生的位置。
Your exception block becomes something like this:-
你的异常块变成这样：-
exception
when exception_pkg.assertion_failure_exception then
rollback;
raise;
when others then
rollback;
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1, 255);
exception_pkg.throw( exception_pkg.unhandled_except, v_code || ' - ' || v_errm || ' ($Header$)' );
... aaaand, here's all the code you need to support this. Play around with it, it's useful :-)
... aaaand，这是您需要支持的所有代码。玩玩它，它很有用:-)
-- Create a table to hold the error messages
CREATE TABLE ERROR_MESSAGES
(
ERROR_MESSAGE_ID NUMBER(10) NOT NULL,
ERROR_DATE TIMESTAMP(6) DEFAULT SYSDATE NOT NULL,
ERROR_USER VARCHAR2(30 BYTE) DEFAULT USER NOT NULL,
MESSAGE_TYPE VARCHAR2(15 BYTE),
PACKAGE_NAME VARCHAR2(250 BYTE),
PROCEDURE_OR_LINE VARCHAR2(30 BYTE),
ERROR_CODE VARCHAR2(10 BYTE),
ERROR_MESSAGE1 VARCHAR2(4000 BYTE),
ERROR_MESSAGE2 VARCHAR2(4000 BYTE),
ERROR_MESSAGE3 VARCHAR2(4000 BYTE),
ERROR_MESSAGE4 VARCHAR2(4000 BYTE)
);
CREATE UNIQUE INDEX ERROR_MESSAGES_XPK ON ERROR_MESSAGES
(ERROR_MESSAGE_ID);
-- Create the sequence used for the ERROR_MESSAGES PK
CREATE SEQUENCE ERROR_MESSAGE_SEQ
START WITH 1;
-- The package
CREATE OR REPLACE PACKAGE EXCEPTION_PKG
as
/************************************************************************************
* $Header$
*
* Package: exception_pkg
*
* Purpose: Exception handling functionality
*
* Authors: M.McAllister (via AskTom - http://tinyurl.com/c43jt)
*
* Revision History:
*
* $Log[10]$
******************************************************************************************/
/*=====================================================================
* Constants
*=====================================================================*/
c_InfMsg constant error_messages.message_type%type := 'Informational';
c_WarnMsg constant error_messages.message_type%type := 'Warning';
c_ErrMsg constant error_messages.message_type%type := 'Fatal Error';
c_DbgMsg constant error_messages.message_type%type := 'Debug';
c_MaintMsg constant error_messages.message_type%type := 'Maintenance';
/*=====================================================================
* Exception Definitions
*=====================================================================*/
unhandled_except constant number := -20001;
unhandled_except_exception exception;
pragma exception_init(unhandled_except_exception, -20001);
bad_parameter constant number := -20002;
bad_parameter_exception exception;
pragma exception_init(bad_parameter_exception, -20002);
assertion_failure constant number := -20003;
assertion_failure_exception exception;
pragma exception_init(assertion_failure_exception, -20003);
/*=====================================================================
* Procedures
*=====================================================================*/
procedure write_exception_info( p_msg_type error_messages.message_type%type
, p_pkg_name error_messages.package_name%type
, p_func_name error_messages.procedure_or_line%type
, p_error_code error_messages.error_code%type
, p_msg1 error_messages.error_message2%type
, p_msg2 error_messages.error_message3%type
, p_msg3 error_messages.error_message4%type
);
procedure who_called_me( p_owner out varchar2,
p_name out varchar2,
p_lineno out number,
p_caller_t out varchar2,
p_my_depth in number default 3
);
procedure throw( p_exception in number
, p_extra_msg in varchar2 default NULL
);
end exception_pkg;
/
-- Package Body
CREATE OR REPLACE PACKAGE BODY EXCEPTION_PKG
as
/************************************************************************************
* $Header$
*
* Package: exception_pkg
*
* Purpose: Exception handling functionality
*
* Authors: M.McAllister (via AskTom - http://tinyurl.com/c43jt)
*
* Revision History:
*
* $Log[10]$
******************************************************************************************/
/*=====================================================================
* Types
*=====================================================================*/
type myArray is table of varchar2(255) index by binary_integer;
/*=====================================================================
* Globals
*=====================================================================*/
err_msgs myArray;
/*=====================================================================
* Procedures
*=====================================================================*/
procedure who_called_me( p_owner out varchar2,
p_name out varchar2,
p_lineno out number,
p_caller_t out varchar2,
p_my_depth in number default 3
)
as
call_stack varchar2(4096) default dbms_utility.format_call_stack;
n number;
found_stack BOOLEAN default FALSE;
line varchar2(255);
cnt number := 0;
begin
loop
n := instr( call_stack, chr(10) );
exit when ( cnt = p_my_depth or n is NULL or n = 0 );
line := substr( call_stack, 1, n-1 );
call_stack := substr( call_stack, n+1 );
if ( NOT found_stack ) then
if ( line like '%handle%number%name%' ) then
found_stack := TRUE;
end if;
else
cnt := cnt + 1;
-- cnt = 1 is ME
-- cnt = 2 is MY Caller
-- cnt = 3 is Their Caller
if ( cnt = p_my_depth ) then
p_lineno := to_number(substr( line, 13, 6 ));
line := substr( line, 21 );
if ( line like 'pr%' ) then
n := length( 'procedure ' );
elsif ( line like 'fun%' ) then
n := length( 'function ' );
elsif ( line like 'package body%' ) then
n := length( 'package body ' );
elsif ( line like 'pack%' ) then
n := length( 'package ' );
elsif ( line like 'anonymous%' ) then
n := length( 'anonymous block ' );
else
n := null;
end if;
if ( n is not null ) then
p_caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
else
p_caller_t := 'TRIGGER';
end if;
line := substr( line, nvl(n,1) );
n := instr( line, '.' );
p_owner := ltrim(rtrim(substr( line, 1, n-1 )));
p_name := ltrim(rtrim(substr( line, n+1 )));
end if;
end if;
end loop;
end who_called_me;
/*=====================================================================
* PRIVATE function: get_session_info
* purpose: Returns a formatted string containing some information
* about the current session
*=====================================================================*/
function get_session_info return varchar2 is
l_sessinfo varchar2(2000);
begin
select
'[SID = ' || sid || '], ' ||
'[SERIAL# = ' || serial# ||'], ' ||
'[MACHINE = ' || replace(machine,chr(0),'') || '], ' ||
'[OSUSER = ' || osuser || '], ' ||
'[PROGRAM = ' || program || '], ' ||
'[LOGON_TIME = ' || to_char(logon_time,'mm/dd/yyyy hh:mi:ss') || ']' into l_sessinfo
from v$session
WHERE audsid = SYS_CONTEXT('userenv','sessionid');
return l_sessinfo;
end get_session_info;
/*=====================================================================
* procedure: write_exception_info
* purpose: Call the exception logging routine
*=====================================================================*/
procedure write_exception_info( p_msg_type error_messages.message_type%type
, p_pkg_name error_messages.package_name%type
, p_func_name error_messages.procedure_or_line%type
, p_error_code error_messages.error_code%type
, p_msg1 error_messages.error_message2%type
, p_msg2 error_messages.error_message3%type
, p_msg3 error_messages.error_message4%type
) is
-- This procedure is autonomous from the calling procedure.
-- i.e The calling procedure does not have to be complete
-- for this procedure to commit its changes.
pragma autonomous_transaction;
l_sessinfo varchar2(2000);
begin
l_sessinfo := get_session_info;
insert into error_messages
( error_message_id
, error_date
, error_user
, message_type
, package_name
, procedure_or_line
, error_code
, error_message1
, error_message2
, error_message3
, error_message4
)
values
( error_message_seq.nextval
, sysdate
, USER
, p_msg_type
, p_pkg_name
, p_func_name
, p_error_code
, l_sessinfo
, p_msg1
, p_msg2
, p_msg3
);
commit;
exception
when others then
-- We don't want an error logging a message to
-- cause the application to crash
return;
end write_exception_info;
procedure throw( p_exception in number
, p_extra_msg in varchar2 default NULL
) is
l_owner varchar2(30);
l_name varchar2(30);
l_type varchar2(30);
l_line number;
l_exception number;
begin
who_called_me( l_owner, l_name, l_line, l_type );
write_exception_info( c_ErrMsg
, l_owner || '.' || l_name
, 'Line ' || l_line
, p_exception
, p_extra_msg
, NULL
, err_msgs(p_exception)
);
raise_application_error( p_exception
, 'Exception at ' || l_type || ' ' ||
l_owner || '.' || l_name || '(' || l_line || '). ' ||
err_msgs(p_exception ) || '. ' || p_extra_msg
, TRUE );
exception
-- we will get this when we have an invalid exception code, one
-- that was not set in the err_msgs array below. The plsql table
-- access will raise the NO-DATA-FOUND exception. We'll catch it,
-- verify the exception code is in the valid range for raise_application_error
-- (if not, set to -20000) and then raise the exception with the message
-- "unknown error"
when NO_DATA_FOUND then
if ( p_exception between -20000 and -20999 ) then
l_exception := p_exception;
else
l_exception := -20000;
end if;
write_exception_info( c_ErrMsg
, l_owner || '.' || l_name
, 'Line ' || l_line
, p_exception
, p_extra_msg
, NULL
, '**UNKNOWN ERROR**'
);
raise_application_error( l_exception
, 'Exception at ' || l_type || ' ' ||
l_owner || '.' || l_name || '(' || l_line || '). ' ||
'**UNKNOWN ERROR**' || '. ' || p_extra_msg
, TRUE );
end throw;
begin
-- This code is run once per session when this package is first touched
err_msgs( unhandled_except ) := 'Unhandled exception';
err_msgs( bad_parameter ) := 'Invalid parameter passed into function or procedure';
err_msgs( assertion_failure ) := 'Program execution stopped due to assertion failure';
end exception_pkg;
/
