如何在 Oracle 中开发一个 after serverror 触发器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/536516/
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 develop an after serverror trigger in Oracle?
提问by xocasdashdash
I'm trying to log all the errors in my database into a table. So as user sys i wrote the following code:
我正在尝试将数据库中的所有错误记录到一个表中。所以作为用户 sys 我写了以下代码：
CREATE TABLE servererror_log (
error_datetime TIMESTAMP,
error_user VARCHAR2(30),
db_name VARCHAR2(9),
error_stack VARCHAR2(2000),
captured_sql VARCHAR2(1000));
/
CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
captured_sql VARCHAR2(1000);
BEGIN
SELECT q.sql_text
INTO captured_sql
FROM gv$sql q, gv$sql_cursor c, gv$session s
WHERE s.audsid = audsid
AND s.prev_sql_addr = q.address
AND q.address = c.parent_handle;
INSERT INTO servererror_log
(error_datetime, error_user, db_name,
error_stack, captured_sql)
VALUES
(systimestamp, sys.login_user, sys.database_name,
dbms_utility.format_error_stack, captured_sql);
END log_server_errors;
But when i force an error like trying to select from a non-existing table it doesn′t log the error in the table.
但是当我强制一个错误，比如试图从一个不存在的表中选择时，它不会在表中记录错误。
Is there any way to check that the trigger fires at all? Also, I tried creating a test table to insert there but it doesn't work either, even if a define the trigger as an autonomous transaction and commit inside the trigger.
有什么方法可以检查触发器是否完全触发？此外，我尝试创建一个测试表以在那里插入，但它也不起作用，即使将触发器定义为自治事务并在触发器内提交。
Thanks, Joaquin
谢谢，华金
回答by Yas
Do not query v$sql; get the statement using ora_sql_txt.
不要查询 v$sql; 使用 ora_sql_txt 获取语句。
CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
sql_text ora_name_list_t;
stmt clob;
n number;
BEGIN
n := ora_sql_txt(sql_text);
if n > 1000 then n:= 1000; end if ;
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;
INSERT INTO servererror_log
(error_datetime, error_user, db_name,
error_stack, captured_sql)
VALUES
(systimestamp, sys.login_user, sys.database_name,
dbms_utility.format_error_stack, stmt);
commit;
END log_server_errors;
/
Then:
然后：
SQL> select * from c;
This produces:
这产生：
select * from c
*
ERROR at line 1:
ORA-00942: table or view does not exist
That can now be queried:
现在可以查询：
select * from servererror_log;
To produce:
生产：
ERROR_DATETIME
---------------------------------------------------------------------------
ERROR_USER DB_NAME
------------------------------ ---------
ERROR_STACK
--------------------------------------------------------------------------------
CAPTURED_SQL
--------------------------------------------------------------------------------
11-FEB-09 02.55.35.591259 PM
SYS TS.WORLD
ORA-00942: table or view does not exist
select * from c
回答by Dave Costa
To see if the trigger is firing, add one or more lines to it like this:
要查看触发器是否正在触发，请向其添加一行或多行，如下所示：
DBMS_OUTPUT.PUT_LINE( 'Got this far' );
In SQLPlus, SET SERVEROUTPUT ON then execute a command to generate an error. You should get output like this:
在 SQLPlus 中，SET SERVEROUTPUT ON 然后执行命令以生成错误。你应该得到这样的输出：
dev> select * from aldfjh;
select * from aldfjh
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-00942: table or view does not exist
Got this far
回答by Dave Jarvis
Save this as ORA-00942.sql:
将其另存为ORA-00942.sql：
-- Drop trigger and ignore errors (e.g., not exists).
DECLARE
existential_crisis EXCEPTION;
PRAGMA EXCEPTION_INIT( existential_crisis, -4080 );
BEGIN
EXECUTE IMMEDIATE 'DROP TRIGGER TRG_CATCH_ERRORS /*+ IF EXISTS */';
EXCEPTION WHEN existential_crisis THEN
DBMS_OUTPUT.PUT_LINE('Ignoring non-existence.');
END;
/
-- Drop table and ignore errors (e.g., not exists).
DECLARE
existential_crisis EXCEPTION;
PRAGMA EXCEPTION_INIT( existential_crisis, -942 );
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE TBL_ERROR_LOG /*+ IF EXISTS */';
EXCEPTION WHEN existential_crisis THEN
DBMS_OUTPUT.PUT_LINE('Ignoring non-existence.');
END;
/
-- Create the table (will not exist due to drop statement).
CREATE TABLE TBL_ERROR_LOG (
occurred timestamp,
account varchar2(32),
database_name varchar2(32),
stack clob,
query clob
);
-- Create the trigger to log the errors.
CREATE TRIGGER TRG_CATCH_ERRORS AFTER servererror ON database
DECLARE
sql_text ora_name_list_t;
n number;
query_ clob;
BEGIN
n := ora_sql_txt( sql_text );
IF n > 1000 THEN n := 1000; END IF;
FOR i IN 1 .. n LOOP
query_ := query_ || sql_text( i );
END LOOP;
INSERT INTO TBL_ERROR_LOG
(occurred, account, database_name, stack, query)
VALUES
(systimestamp, sys.login_user, sys.database_name,
dbms_utility.format_error_stack, query_);
END;
/
Run using sqlplus:
使用 sqlplus 运行：
SQL> @ORA-00942.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Table created.
Trigger created.
Test it:
测试一下：
select * from blargh;
select * from TBL_ERROR_LOG;
Output:
输出：
2017-10-20 15:15:25.061 SCHEMA XE "ORA-00942: table or view does not exist" select * from blargh
回答by Edwin
Check the status of your trigger and/or the existence of other triggers with:
检查触发器的状态和/或其他触发器的存在：
select trigger_name, status
from all_triggers
where triggering_event like 'ERROR%'
This should result into:
这应该导致：
TRIGGER_NAME STATUS
------------ -------
LOG_SERVER_ERRORS ENABLED
If trigger is not enabled or another trigger fails, it probably will not work.
如果触发器未启用或另一个触发器失败，则它可能无法工作。
