gpt4 book ai didi

oracle - 如何在 Oracle 中开发一个 after serverror 触发器?

转载 作者:行者123 更新时间:2023-12-04 15:28:38 29 4
gpt4 key购买 nike

我正在尝试将数据库中的所有错误记录到一个表中。所以作为用户 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;

但是,当我强制执行错误,例如尝试从不存在的表中进行选择时,它不会在表中记录错误。

有什么方法可以检查触发器是否完全触发?此外,我尝试创建一个测试表以插入到那里,但它也不起作用,即使将触发器定义为自治事务并在触发器内提交也是如此。

谢谢,
华金

最佳答案

不要查询 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;
/

然后:
SQL> select * from c;

这产生:
select * from c
*
ERROR at line 1:
ORA-00942: table or view does not exist

现在可以查询:
select * from servererror_log;

生产:
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

关于oracle - 如何在 Oracle 中开发一个 after serverror 触发器?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/536516/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com