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)
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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in November 2002