Koen Aerts

Sometimes there is a need to find out exactly which Oracle errors are occurring in your code, because your application stacktrace or your log files are not providing the details you’re looking for. In Oracle, this can be achieved by placing a trigger on the “servererror” event and which stores the details of the error in a table. The downside of this is that you need sys or system privileges to the database, and as a developer you may not always have these privileges on the database you’re working with.

Log on as sys or system user to the Oracle DB and create the following table:

CREATE TABLE AAA_CAUGHT_ERRORS (
  DT        DATE,
  USERNAME  VARCHAR2(30),
  MSG       VARCHAR2(2000),
  STMT      VARCHAR2(2000)
);

Next, create a trigger that runs for each error on the database, and populates the above table with the error details.

create or replace trigger aaa_catch_errors
after servererror on database
declare
  sql_text ora_name_list_t;
  msg_     varchar2(2000) := null;
  stmt_    varchar2(2000) := null;
begin
  for depth in 1 .. ora_server_error_depth loop
    msg_ := msg_ || ora_server_error_msg(depth);
  end loop;
  for i in 1 .. ora_sql_txt(sql_text) loop
    stmt_ := stmt_ || sql_text(i);
  end loop;
  insert into aaa_caught_errors
    (dt, username, msg, stmt)
  values (sysdate, ora_login_user, msg_, stmt_);
end;
/

Example of some errors caught during execution of the faulty application:

DT,USERNAME,MSG,STMT
10-12-17,JBOSSAS_3,"ORA-00902: invalid datatype","CREATE TABLE JBM_COUNTER (NAME VARCHAR(255), NEXT_ID BIGINT, PRIMARY KEY(NAME))"
10-12-17,JBOSSAS_3,"ORA-00955: name is already used by an existing object","CREATE TABLE JBM_ID_CACHE (NODE_ID INTEGER, CNTR INTEGER, JBM_ID VARCHAR(255), PRIMARY KEY(NODE_ID, CNTR))"
10-12-17,JBOSSAS_3,"ORA-00001: unique constraint (JBOSSAS_3.SYS_C009366) violated","INSERT INTO JBM_DUAL VALUES (1)"