Logging the command that caused server error |
 |
EXPERT RESPONSE FROM: Karen Morton
|
|
|
| > |
QUESTION POSED ON: 18 November 2002
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?
|
|
| > |
|
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
|
|
|
|
|
|
|
|
Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and
answer pairs from more than 250 TechTarget industry experts.
|
|
|
|
|
|
|
|
|
|
|
|
