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






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