Capture Oracle Quick Terminated Process

It is hard to detect application connections which terminate upon encounter Oracle error, or application error related to its design. Following database level trigger will be able to capture them into a custom table, and review later.

Another application is where user call some procedure and encounter error. It is hard to go after each user to find out what parameter they use, especially it is call from application. It will be costly to engage developer/vendor.

This is just a skeleton which can be further improve by selecting the useful columns

create trigger error_log AFTER SERVERERROR ON DATABASE
declare
sql_statement varchar2(4000);
BEGIN
begin
  SELECT USER||':'||dbms_standard.server_error(1)||':'||q.sql_text
  INTO sql_statement
  FROM gv_$sql q,  gv_$sql_cursor c, gv_$session s
  WHERE s.audsid = audsid
  AND s.prev_sql_addr = q.address
  AND q.address = c.parent_handle;
exception when no_data_found then
   sql_statement := USER||':'||dbms_standard.server_error(1)||':'||'SQL is not in SQL cursor';
end;
myproc.save_err_line (sql_statement);
myproc.save_err_line (DBMS_UTILITY.format_error_backtrace);

Where myproc.save_err_line is a simple procedure to insert the given string into a custom table. The custom table only contain a varchar2 column