Log a server error to a table : Database Trigger « Trigger « Oracle PL / SQL

1. Oracle PL / SQL
2. Trigger
3. Database Trigger

Log a server error to a table

  
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> BEGIN
  2    -- This is a syntax error!
  3    DELETE FROM students
  4  END;
  5  /
END;
   *
ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe


SQL>
SQL>
SQL> SELECT * FROM error_log;

TIMESTAMP USERNAME                         INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
18-JUN-08 JAVA2S                                  1
XE
ORA-06550: line 4, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expectin
g one of the following:

   begin case declare end exception exit for goto if loop mod

TIMESTAMP USERNAME                         INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------

   null pragma raise return select update while with
   <an identifier> <a double-quoted delimited-identifier>
   <a bind variable> << close current delete fetch lock inser
t
   open rollback savepoint set sql execute commit forall merg
e

TIMESTAMP USERNAME                         INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
   pipe


SQL>
SQL> DROP TABLE error_log;

Table dropped.

SQL>
SQL> drop trigger LOGERRORS;

Trigger dropped.

   
  

Related examples in the same category