Using Database Trigger on Servererror to track all Oracle Errors

From: Pete M <petermerks_at_hotmail.com>
Date: 31 Aug 2001 11:54:58 -0700
Message-ID: <e001850d.0108311054.1ef8ffcb@posting.google.com>

I want to create a trigger that writes to a table or file whenever an Oracle error happens. The main thing I want to capture is the offending SQL. Users are reporting these errors out of an application erratically and we don't know the call that is causing the error.

Does anyone know how to catch the offending SQL? How about capturing any error message code - as in SQLCODE or SQLERRM which always seems to be 0.

Here is a code sample. The pl/sql will run however I can't capture what I am really interested in.

create or replace trigger log_all_servererrors after servererror on database
declare

  v_output utl_file.file_type;
  v_sqlcode varchar2(250);
  v_sqlerrm varchar2(250);

begin
  v_sqlcode:=SQLCODE;
  v_sqlerrm:=SQLERRM;
   if (IS_SERVERERROR (904)) then

     v_output := utl_file.fopen('C:\TEMP','log_all_servererrors.txt','a');
     utl_file.put_line(v_output,'error has occurred');
     utl_file.put_line(v_output,sysdate);
     /* this is where I want to put in the offending SQL */
     utl_file.new_line(v_output);
     utl_file.fclose (v_output);

   end if;
end;
/ Received on Fri Aug 31 2001 - 13:54:58 CDT