Trigger on database to capture ORA errors

You might want to track ORA errors on your database so that whenever an oracle error comes,you may want the error code and error description to be logged into some table. This will be required when you see your application just exists saying some error but you did not see any ORA error in application log file or console.

In this cases, what you can do is write a trigger on the database server to log the error code, error statement and error description to a table when ORA error comes.

To implement this, first create a table which will hold the ORA error,statement and error description. This can be created in SYS schema.

CREATE TABLE stats$error_log (
        err_dt          TIMESTAMP,
        db_user         VARCHAR2(30),
        msg_stack       VARCHAR2(2000),
        sqltxt          VARCHAR2(1000))
tablespace users;

Now, create a trigger on the database server error.

CREATE OR REPLACE TRIGGER log_server_errors
  AFTER SERVERERROR
  ON DATABASE
DECLARE
          v_sqltext VARCHAR2(1000);
          nl_sqltext ora_name_list_t;
  BEGIN
          -- Capture entire error text
          FOR i IN 1 .. ora_sql_txt(nl_sqltext) LOOP
            v_sqltext := v_sqltext || nl_sqltext(i);
          END LOOP;
          INSERT INTO STATS$ERROR_LOG
          (err_dt, db_user, msg_stack, sqltxt)
          VALUES
          (systimestamp,
           sys.login_user,
           dbms_utility.format_error_stack, v_sqltext);
  END log_server_errors;
/

For further reference you can find.
Simple version:
http://www.adp-gmbh.ch/ora/sql/trigger/servererror.html

Slightly more fancy version:
http://blog.sydoracle.com/2011/02/using-oracles-servererror-to-catch.html