Re: Using Database Trigger on Servererror to track all Oracle Errors

From: Pete M <petermerks_at_hotmail.com>
Date: 31 Aug 2001 19:08:34 -0700
Message-ID: <e001850d.0108311808.33c03c68@posting.google.com>

I've revised the trigger to properly capture the Oracle Error but I'd really still like to be able to capture the actual offending SQL?

Does anyone have any ideas?

Users for this one application are reporting very sporadic ORA-00904 errors. I know what the error is but I'd really like to find the offending SQL statements.

create or replace trigger log_all_servererrors after servererror on database
declare

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

begin

v_sqlcode1:=server_error(1);
v_sqlerrm :=SQLERRM;
v_output := utl_file.fopen('C:\TEMP','log_all_servererrors.txt','a');
utl_file.put_line(v_output,'USER : '||user);
utl_file.put_line(v_output,'DATE :

'||to_char(sysdate,'YYYYMMDD:HH24:MI'));

utl_file.put_line(v_output,'ERROR: '||v_sqlcode1);
utl_file.new_line(v_output);
utl_file.fclose (v_output);

end; Received on Fri Aug 31 2001 - 21:08:34 CDT