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: