Mega Code Archive
Log a server error to a table
<![CDATA[
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 RNTSOFT 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.
]]>