Read your answer about logging SQL statements with the help of a trigger... I can think of one possible solution. If the user can log the last HASH VALUE in a table, in the event of error, the trigger gets the SQL statement using the last stored HASH_VALUE for that user. This should give the last SQL STATEMENT executed by that user and also not the one that is being used to select the information about user in the TRIGGER. Hope this works. In this case, there is some overhead and also some additional processing required but yes, you can trap the error SQL to some extent. Isn't it true?
Did you try it and if so, did it work consistently all the time? How did you capture the "correct" hash value? Did you get it from V$SESSION? Were you able to get just a single row back that pointed to the correct SQL statement every time?
In limited testing, I had a bit of trouble with these things, but admittedly didn't fully attempt to test/implement such a plan. If you've got the code working, we'd love to have you share it with us and send it in as a tip so that everybody could benefit!
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.
Dig Deeper on Oracle error messages
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.