use DBMS_UTILITY.FORMAT_ERROR_STACK in a SERVERERROR trigger.

   
SQL>
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> SELECT * FROM non_existent_table;
SELECT * FROM non_existent_table
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> BEGIN
  2    INSERT INTO non_existent_table VALUES ('Hello!');
  3  END;
  4  /
  INSERT INTO non_existent_table VALUES ('Hello!');
              *
ERROR at line 2:
ORA-06550: line 2, column 15:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored


SQL> COLUMN error_stack FORMAT a61 WRAPPED
SQL> SET LINE 80
SQL>
SQL> SELECT * FROM error_log;

TIMESTAMP USERNAME                         INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
18-JUN-08 JAVA2S                                  1
XE
ORA-00942: table or view does not exist

18-JUN-08 JAVA2S                                  1
XE
ORA-06550: line 2, column 15:

TIMESTAMP USERNAME                         INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored


SQL>
SQL> DROP TABLE error_log;

Table dropped.

SQL>
SQL> drop trigger LOGERRORS;

Trigger dropped.

SQL>

Related examples in the same category

1.	This script demonstrates DBMS_UTILITY.NAME_TOKENIZE.
2.	Use dbms_utility to time
3.	time to commit
4.	dbms_utility.get_parameter_value
5.	Call dbms_utility.get_time twice to time a statement in PL SQL
6.	round dbms_utility.get_time
7.	Assign dbms_utility.get_time to integer variable
8.	Use dbms_utility.get_time to do performace check
9.	dbms_utility.format_error_stack
10.	Timing Per Thousand Records Processed (in secs)
11.	Timing Package function call
12.	DBMS_UTILITY.analyze_schema
13.	demonstrates the use of DBMS_UTILITY.TABLE_TO_COMMA and DBMS_UTILITY.COMMA_TO_TABLE.
14.	Performace difference between simple parameter and collection parameter

use DBMS_UTILITY.FORMAT_ERROR_STACK in a SERVERERROR trigger. : dbms_utility « System Packages « Oracle PL / SQL

Related examples in the same category