Log a server error with information on time, user name, database name

  
SQL>
SQL> CREATE TABLE error_log (
  2    timestamp     DATE,
  3    username      VARCHAR2(30),
  4    instance      NUMBER,
  5    database_name VARCHAR2(50),
  6    error_stack   VARCHAR2(2000)
  7    );

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER LogErrors
  2    AFTER SERVERERROR ON DATABASE
  3  BEGIN
  4    INSERT INTO error_log
  5      VALUES (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.
  6              DATABASE_NAME, DBMS_UTILITY.FORMAT_ERROR_STACK);
  7  END LogErrors;
  8  /

Trigger created.

SQL>
SQL>
SQL> DECLARE
  2    v_StringVar VARCHAR2(2);
  3  BEGIN
  4    -- This is a runtime error!
  5    v_StringVar := 'abcdef';
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5


SQL>
SQL>
SQL>
SQL> SELECT * FROM error_log;

TIMESTAMP USERNAME                         INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
18-JUN-08 JAVA2S                                  1
XE
ORA-06502: PL/SQL: numeric or value error: character string b
uffer too small
ORA-06512: at line 5


SQL>
SQL> DROP TABLE error_log;

Table dropped.

SQL>
SQL> drop trigger LOGERRORS;

Trigger dropped.

Related examples in the same category

1.	Trigger for database startup event
2.	Trigger to log the database shutdown
3.	Logon trigger
4.	After logon database trigger
5.	Log database logon to a table
6.	Log a server error to a table
7.	A system trigger(AFTER CREATE ON DATABASE) .
8.	creating a logon/logoff auditing system using system-level triggers:
9.	Save information about all errors in an after-servererror on database
10.	Submit a job for altering user in an after-create-database trigger

Log a server error with information on time, user name, database name : Database Trigger « Trigger « Oracle PL / SQL

Related examples in the same category