Logging the command that caused server error

How can I log the command/SQL statement that caused the server error? I have a trigger, on after servererror, which logs the ERROR, TIMESTAMP, USERNAME, OSUSER, MACHINE, PROCESS, PROGRAM in the log_error table. In addition to this, I also want to log the command/SQL statement which caused the error. How can I incorporate that info into the trigger? I tried the following SQL to get the SQL text:
select A.username, A.osuser, A.machine, A.process, A.program,B.sql_text 
  into var_user, var_osuser, var_machine, var_process, 
       var_program, var_sqltext
  from sys.v_$session A,sys.v_$sqlarea B
where A.audsid = userenv('sessionid') 
  and B.hash_value = A.sql_hash_value
  and B.address = A.sql_address;

insert into
log_errors_tab(ERROR,TIMESTAMP,USERNAME,OSUSER,MACHINE,
               PROCESS,PROGRAM,SQL_TEXT)
values(dbms_standard.server_error(1),sysdate,var_user,var_osuser,var_machine,var_process,
 var_program,var_sqltext);
But this always put the above SQL in the table, not the error one. Is there a way to log the SQL statement that caused the error? Also, is it possible to know the values of the bind variables in the SQL query when the query was executed?

    Requires Free Membership to View

    Login

The reason why your code doesn't work is that when you check v$sesion and v$sqlarea, you are getting the currently executing statement... not the one that was executing at the time the SERVERERROR trigger executed. The only "exact" way to get the contents of the statement that was executed when the error occurred is to 1) capture it before you execute it in some global package variable so you can then have it available when the SERVERERROR trigger fires or 2) gather all the pieces of information in an INSERT/UPDATE/DELETE trigger and "rebuild" the statement context. I don't know of a way to absolutely capture the exact SQL statement that caused the error easily.

But... to be able to get the basic context of the SQL that was executed, you could simply use one or more of the system defined event attributes that are only available for use in trigger code. Some examples are:

  • ora_sysevent = The event that was happening when the error occurred (INSERT, UPDATE, etc)
  • ora_dict_obj_name = The name of the object that was being effected (EMP, DEPT, etc)
  • ora_dict_obj_type = The type of the object that was being effected (TABLE, VIEW, etc)
There are a whole bunch more which you can review in the Oracle documentation "Application Developers Guide - Fundamentals", chapter 13.

You can use these system event attributes in INSERT/UPDATE/DELETE triggers to determine what was done, who did it and to which object. You can even use the :new and :old trigger variables to capture column values. Once you get to the SERVERERROR trigger, you don't have access to all the ora_ values (again, see chapter 13 of the document referenced above).

Maybe you need to think about why you want the SQL statement logged and try another approach... like maybe you could use Oracle's auditing features or DBMS_APPLICATION_INFO to put values into the module and action columns in v$session.

For More Information


This was first published in November 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top