Trigger to catch and log SQL errors in Oracle in the background

log_server_errors_tg.sql

-- See http://wp.me/pojaY-8G (German)
DROP TABLE servererror_log
/
CREATE TABLE servererror_log (
  error_datetime TIMESTAMP,
, error_user VARCHAR2(30)
, error_stack VARCHAR2(2000)
, error_backtrace VARCHAR2(4000)
, captured_sql VARCHAR2(4000))
/
CREATE OR REPLACE
TRIGGER log_server_errors
AFTER SERVERERROR
ON SCHEMA -- oder auch "ON DATABASE"
DECLARE
  PRAGMA autonomous_transaction;
  sql_text DBMS_STANDARD.ora_name_list_t;
  n PLS_INTEGER;
  v_stmt VARCHAR2(4000);
BEGIN
  -- Das problematische SQL...
  n := ora_sql_txt(sql_text);
  -- ... besteht ggf. aus mehreren Teilen und wird in einer Schleife wieder zusammengefügt
  FOR i IN 1..n LOOP
    v_stmt := v_stmt || sql_text(i);
  END LOOP;

  INSERT INTO servererror_log
   ( error_datetime
   , error_user
   , error_stack
   , error_backtrace
   , captured_sql )
  VALUES
   ( systimestamp
   , sys.login_user
   , dbms_utility.format_error_stack
   , dbms_utility.format_error_backtrace
   , v_stmt );
  COMMIT;
END log_server_errors;
/