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

From: Cory Brooks <coryb_at_nc.rr.com>
Date: Sat, 01 Sep 2001 09:09:01 GMT
Message-ID: <NA1k7.203720$TM5.37655237@typhoon.southeast.rr.com>

Over a year ago, I tried to capture the offending sql with the following: select ..., v$sql.sql_text

       from
        v$session v$ses,  v$process v$proc,   v$sqlarea  v$sql
       where  ...
         and  v$ses.sql_address     = v$sql.address(+)
         and  v$ses.sql_hash_value  = v$sql.hash_value  and

v$ses.sql_hash_value <> 0

Unfortunately, it picks up the currently executing sql which is the above command. Conveniently, v$session has the following columns: PREV_SQL_ADDR PREV_HASH_VALUE. The bad news is that they point to the same area (the above command) rather than the sql that was executing at the time of the server error. I reported it in a tar and was informed that it was an error. I believe that's where things still stand today.

One difference, in my approach was that the above command was part of an insert so that the server error log could be analyzed. Since you're using utl_file to write the log outside the database, you may have more luck. Cory

"Pete M" <petermerks_at_hotmail.com> wrote in message news:e001850d.0108311808.33c03c68_at_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 Sat Sep 01 2001 - 04:09:01 CDT