Trace files for specific errors

Hi all,

I have an app that is experiencing ORA-00907 and ORA-00911, both of which relate to syntax errors in SQL statements (missing parentheses, unquoted special characters etc). I don't want to switch on SQL tracing globally, and I won't know which session generated the error until it's already happened, so there's no point enabling tracing in a single session.

I need to see the SQL (which is generated by a Java program and invoked via JDBC) so that I can identify the source of the error. From an earlier post on this NG concerning ORA-00911, I see that someone suggesting adding a line like this:

event=911 trace name errorstack level 3

to the INIT.ORA file. Although it doesn't actually say so, I'm guessing that this will cause Oracle to generate a trace file every time it hits ORA-00911, which would suit me fine because the trace file would contain the statement that caused the problem, wouldn't it?

The idea of trace files for specific errors is new to me, so I'd appreciate any pointers you can give me. Is INIT.ORA the best/only way to do this is 9iR2? Does the line have to go in a certain section of the file? Do I have any control over the name of the trace file?

TIA
Nick Received on Thu Nov 10 2005 - 07:46:28 CST