Using SERVERERROR Trigger to troubleshooting application error like ORA-00001

oracle@solaris:~$ sqlplus /

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 20 22:57:52 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serveroutput on
SQL> set echo on
SQL> @trigger.sql

SQL> CREATE TABLE servererror_log (
  2      error_datetime  TIMESTAMP,
  3      error_user      VARCHAR2(30),
  4      db_name         VARCHAR2(9),
  5          l_server_error  number,
  6      error_stack     VARCHAR2(2000),
  7      captured_sql    VARCHAR2(1000))
  8  /

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER log_server_errors
  2  AFTER SERVERERROR
  3  ON DATABASE
  4  DECLARE
  5  sql_text ora_name_list_t;
  6  stmt clob;
  7  n number;
  8  l_server_error number;
  9  BEGIN
 10    n := ora_sql_txt(sql_text);
 11    if n > 1000 then n:= 1000; end if ;
 12    FOR i IN 1..n LOOP
 13      stmt := stmt || sql_text(i);
 14    END LOOP;
 15
 16    l_server_error := server_error(1);
 17
 18    INSERT INTO servererror_log
 19    (error_datetime, error_user, db_name,l_server_error,
 20     error_stack, captured_sql)
 21    VALUES
 22    (systimestamp, sys.login_user, sys.database_name,l_server_error,
 23    dbms_utility.format_error_stack, stmt);
 24    commit;
 25  END log_server_errors;
 26  /

Trigger created.

SQL>
SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 (id number primary key);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (1);
insert into t1 values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$ORACLE.SYS_C0011730) violated


SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';

Session altered.

SQL> select * from servererror_log;

ERROR_DATETIME
---------------------------------------------------------------------------
ERROR_USER                     DB_NAME   L_SERVER_ERROR
------------------------------ --------- --------------
ERROR_STACK
--------------------------------------------------------------------------------
CAPTURED_SQL
--------------------------------------------------------------------------------
20-FEB-14 10.58.14.724928 PM
OPS$ORACLE                     ORCL                   1
ORA-00001: unique constraint (OPS$ORACLE.SYS_C0011730) violated
insert into t1 values (1)