[每日一题] PL/SQL Challenge 每日一题：2016-8-9 SERVERERROR 触发器 学习笔记

题目源地址http://www.itpub.net/thread-2065472-1-1.html

题目

(原发表于 2011-5-3) 最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。 每两周的优胜者可获得itpub奖励的技术图书一本。 以往旧题索引： http://www.itpub.net/forum.php?m ... eid&typeid=1808 原始出处： http://www.plsqlchallenge.com/ 作者：ga_myers 运行环境：SQLPLUS, SERVEROUTPUT已打开 注：本题给出答案时候要求给予简要说明才能得到奖品  我创建了如下的表： CREATE TABLE plch_errors (err_num number); 然后我创建了一个 SERVERERROR 触发器 (我的schema有所需的权限)，它会记录错位堆栈顶端的错误代码。 CREATE OR REPLACE TRIGGER log_err    AFTER SERVERERROR    ON SCHEMA DECLARE    v_num NUMBER; BEGIN    v_num:=ora_server_error (1);    INSERT INTO plch_errors (err_num)         VALUES (ABS (v_num)); END; / 然后下列三个PL/SQL块被执行，抛出了VALUE_ERROR 和 TOO_MANY_ROWS异常。 DECLARE    v_test VARCHAR2 (2); BEGIN    v_test := '123'; END; / DECLARE    v_test VARCHAR2 (2); BEGIN    v_test := '123'; EXCEPTION    WHEN OTHERS THEN NULL; END; / DECLARE    v_test VARCHAR2 (3); BEGIN    SELECT 'NO' INTO v_test FROM sys.DUAL    UNION    SELECT 'YES' FROM sys.DUAL; END; / 下列查询执行之后，输出显示什么？ SELECT err_num     FROM plch_errors ORDER BY err_num / (A)  1422 6502 (B)  1422 6502 6502 (C)  1422 (D)  6502 (E)  6502 6502 答案： 当一个服务端错误从SQL或者PL/SQL层被传播出来，SERVERERROR触发器就会触发。这个触发器是在schema定义的，所以它会为所有连接到那个schema的用户所触发，不管执行的是哪个应用。 SERVERERROR可以用来捕获那些在设计和开发时没有想到的错误事件。 SERVERERROR也可以定义在整个数据库实例，所以它能用作某种“万能”的错误日志，记录这个实例上执行的任何错误。但是它也有些令人意外的表现。 具体地说，如同Oracle文档中所言，下列错误不会引发SERVERERROR触发器： ORA-01403: no data found (NO_DATA_FOUND) ORA-01422: exact fetch returns more than requested number of rows (TOO_MANY_ROWS) ORA-01423: error encountered while checking for extra rows in exact fetch ORA-01034: ORACLE not available ORA-04030: out of process memory when trying to allocate string bytes (string, string) Oracle 解释说这个触发器没有触发是“因为它们并不是真正的错误，或者不至于严重到无法继续处理。 ORA-18 和 ORA-20也不会触发，因为此时没有进程可连接到数据库来记录错误。” 结果，本例中第一个块的ORA-06502 (VALUE_ERROR)在表中被记录为6502。第二个块抛出了同样的异常，但是被捕获并且处理。因为块中没有抛出另一个（相同）的异常，所以SERERERROR没有触发。 最后，两个在sys.DUAL上的SELECT的UNION导致的TOO_MANY_ROWS异常并不会触发SERVERERROR。 详解： http://blog.csdn.net/indexman/article/details/8023740/ 这里是对触发器的讲解。 以下是一位的博客，讲解关于，servererror trigger，请原谅我的无知，不知道他是谁。地址如下： http://www.adp-gmbh.ch/ora/sql/trigger/servererror.html Servererror trigger [Oracle] The  servererror system event trigger is fired when an error is encountered. This trigger is not fired, however, for the  ORA-01034 ORACLE not available,  ORA-01403 no data found,  ORA-01422 exact fetch returns more than requested number of rows,  ORA-01423 error encountered while checking for extra rows in exact fecht and ORA-04030 out of process memory when trying to allocate stringbytes. If an error occurs and the trigger is fired, but the trigger cannot be executed successfully, the original error is prepended with a ORA-04045 error during recompilation/revalidation of %s%s. An example A table is created that will then be filled by the trigger when an error has occured. create table caught_errors ( dt date, username varchar2( 30), -- value from ora_login_user msg varchar2(512), stmt varchar2(512) ); This  trigger will actually fill the table: create or replace trigger catch_errors after servererror on database declare sql_text ora_name_list_t; msg_ varchar2(2000) := null; stmt_ varchar2(2000) := null; begin for depth in 1 .. ora_server_error_depth loop msg_ := msg_ || ora_server_error_msg(depth); end loop; for i in 1 .. ora_sql_txt(sql_text) loop stmt_ := stmt_ || sql_text(i); end loop; insert into caught_errors (dt , username ,msg ,stmt ) values (sysdate, ora_login_user,msg_,stmt_); end; / Now, a select statement is executed on a table that does not exist: SQL> select * from foijf; select * from foijf * ERROR at line 1: ORA-00942: table or view does not exist This error is now reported in caught_errors: SQL> set lines 100 SQL> select dt, username, substr(stmt, 1, 40) from caught_errors; DT USERNAME SUBSTR(STMT,1,40) --------- ------------------------------ ---------------------------------------- 25-MAR-05 RENE select * from foijf AFTER SERVERERROR 以下为官方文档对after servererror触发触发器的两点说明。 AFTER SERVERERROR Causes the database to fire the trigger whenever both of these conditions are true: A server error message is logged. Oracle relational database management system (RDBMS) determines that it is safe to fire error triggers. Examples of when it is unsafe to fire error triggers include: RDBMS is starting up. A critical error has occurred. ON SCHEMA 表明是作用在用户级别。 ora_server_error(1) Attribute Return Type and Value Example ora_server_error ( position IN PLS_INTEGER ) NUMBER: Error code at given position on error stack INSERT INTO event_table VALUES ('top stack error ' || ora_server_error(1)); http://docs.oracle.com/database/121/LNPLS/triggers.htm#LNPLS794 官方文档。

(原发表于 2011-5-3) 最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。 每两周的优胜者可获得itpub奖励的技术图书一本。 以往旧题索引： http://www.itpub.net/forum.php?m ... eid&typeid=1808 原始出处： http://www.plsqlchallenge.com/ 作者：ga_myers 运行环境：SQLPLUS, SERVEROUTPUT已打开 注：本题给出答案时候要求给予简要说明才能得到奖品  我创建了如下的表： CREATE TABLE plch_errors (err_num number); 然后我创建了一个 SERVERERROR 触发器 (我的schema有所需的权限)，它会记录错位堆栈顶端的错误代码。 CREATE OR REPLACE TRIGGER log_err    AFTER SERVERERROR    ON SCHEMA DECLARE    v_num NUMBER; BEGIN    v_num:=ora_server_error (1);    INSERT INTO plch_errors (err_num)         VALUES (ABS (v_num)); END; /  然后下列三个PL/SQL块被执行，抛出了VALUE_ERROR 和 TOO_MANY_ROWS异常。 DECLARE    v_test VARCHAR2 (2); BEGIN    v_test := '123'; END; / DECLARE    v_test VARCHAR2 (2); BEGIN    v_test := '123'; EXCEPTION    WHEN OTHERS THEN NULL; END; / DECLARE    v_test VARCHAR2 (3); BEGIN    SELECT 'NO' INTO v_test FROM sys.DUAL    UNION    SELECT 'YES' FROM sys.DUAL; END; / 下列查询执行之后，输出显示什么？ SELECT err_num     FROM plch_errors ORDER BY err_num / (A)  1422 6502 (B)  1422 6502 6502 (C)  1422 (D)  6502 (E)  6502 6502 答案： 当一个服务端错误从SQL或者PL/SQL层被传播出来，SERVERERROR触发器就会触发。这个触发器是在schema定义的，所以它会为所有连接到那个schema的用户所触发，不管执行的是哪个应用。 SERVERERROR可以用来捕获那些在设计和开发时没有想到的错误事件。 SERVERERROR也可以定义在整个数据库实例，所以它能用作某种“万能”的错误日志，记录这个实例上执行的任何错误。但是它也有些令人意外的表现。 具体地说，如同Oracle文档中所言，下列错误不会引发SERVERERROR触发器： ORA-01403: no data found (NO_DATA_FOUND) ORA-01422: exact fetch returns more than requested number of rows (TOO_MANY_ROWS) ORA-01423: error encountered while checking for extra rows in exact fetch ORA-01034: ORACLE not available ORA-04030: out of process memory when trying to allocate string bytes (string, string) Oracle 解释说这个触发器没有触发是“因为它们并不是真正的错误，或者不至于严重到无法继续处理。 ORA-18 和 ORA-20也不会触发，因为此时没有进程可连接到数据库来记录错误。” 结果，本例中第一个块的ORA-06502 (VALUE_ERROR)在表中被记录为6502。第二个块抛出了同样的异常，但是被捕获并且处理。因为块中没有抛出另一个（相同）的异常，所以SERERERROR没有触发。 最后，两个在sys.DUAL上的SELECT的UNION导致的TOO_MANY_ROWS异常并不会触发SERVERERROR。 详解： http://blog.csdn.net/indexman/article/details/8023740/ 这里是对触发器的讲解。 以下是一位的博客，讲解关于，servererror trigger，请原谅我的无知，不知道他是谁。地址如下： http://www.adp-gmbh.ch/ora/sql/trigger/servererror.html Servererror trigger [Oracle] The  servererror system event trigger is fired when an error is encountered. This trigger is  not fired, however, for the  ORA-01034 ORACLE not available,  ORA-01403 no data found,  ORA-01422 exact fetch returns more than requested number of rows,  ORA-01423 error encountered while checking for extra rows in exact fecht and  ORA-04030 out of process memory when trying to allocate stringbytes. If an error occurs and the trigger is fired, but the trigger cannot be executed successfully, the original error is prepended with a ORA-04045 error during recompilation/revalidation of %s%s. An example A table is created that will then be filled by the trigger when an error has occured. create table caught_errors ( dt date, username varchar2( 30), -- value from ora_login_user msg varchar2(512), stmt varchar2(512) ); This  trigger will actually fill the table: create or replace trigger catch_errors after servererror on database declare sql_text ora_name_list_t; msg_ varchar2(2000) := null; stmt_ varchar2(2000) := null; begin for depth in 1 .. ora_server_error_depth loop msg_ := msg_ || ora_server_error_msg(depth); end loop; for i in 1 .. ora_sql_txt(sql_text) loop stmt_ := stmt_ || sql_text(i); end loop; insert into caught_errors (dt , username ,msg ,stmt ) values (sysdate, ora_login_user,msg_,stmt_); end; / Now, a select statement is executed on a table that does not exist: SQL> select * from foijf; select * from foijf * ERROR at line 1: ORA-00942: table or view does not exist This error is now reported in caught_errors: SQL> set lines 100 SQL> select dt, username, substr(stmt, 1, 40) from caught_errors; DT USERNAME SUBSTR(STMT,1,40) --------- ------------------------------ ---------------------------------------- 25-MAR-05 RENE select * from foijf AFTER SERVERERROR 以下为官方文档对after servererror触发触发器的两点说明。 AFTER SERVERERROR Causes the database to fire the trigger whenever both of these conditions are true: A server error message is logged. Oracle relational database management system (RDBMS) determines that it is safe to fire error triggers. Examples of when it is unsafe to fire error triggers include: RDBMS is starting up. A critical error has occurred.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29074224/viewspace-2123458/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29074224/viewspace-2123458/