Howard went on to offer some great advice as usual to grab the SQL from the SGA or by using Log Miner.
I added the following:
"Howard has given some good advice but let me give some other tips. The
first thing is that the error number you list looks like a Windows
error, at least that is the sort of number I see when Windows programs
crash. It could be that the application tool parses the SQL first and
the error is detected before sending the SQL to the server so you may
not find it in the database or on the way to the database.
If you can repeat the problem - I think from inference you can then set
SQL*Net trace on the client that is running the application. An example
of how to do this is in my paper "Detecting SQL Injection in Oracle"
which you can find at /orasec.htm - This
trace will then contain the SQL statement sent to the server from the
application. You can also use SQL trace (depending on how far the SQL
got into the server) - a paper on many ways to set trace is at
/ramblings/how_to_set_trace.htm
Finally if you are a bit more adventurous and you application uses OCI
as its lowest layer then there is a free tool that grabs SQL from the
OCI layer called OCISPY, you can find a link on my tools page at
/tools.htm - There is a Java tool that does
the same for JDBC listed there as well, sorry cannot recall the exact
name at the moment."
Finally Niall gave some good advice that went further to suggest that the issue may be in the client.
This is a good subject and one that comes up from time to time. Quite often third party applications where the course code is not available are used in organisations and its difficult to understand exactly what these applications are doing database wise. This can often be an issue for tuning the application but also often in security contexts where the exact data translations and queries need to be known. Just because an application is a black box does not mean that we cannot work out how it talks to the database, and as such this interests me from a security perspective.
There are a number of possibilities of extracting the SQL generated from an application as have been discussed in this thread. These can be summarised as follows:
- Extract the SQL from the SGA
- Extracting the SQL from the redo with Log Miner
- Use Oracle Net trace to grab the SQL off the wire
- Use Oracle database SQL trace to grab the SQL as its executed
- Use tools like OCI SPY to grab the SQL from the Oracle client before its despatched to the Oracle Network stack
There are a few more options that I have thought about since I made my post, probably there are others as well.
- It is also possible to grab the SQL from the network with a network packet sniffer such as ethereal or snoop
- I am (almost?) certain OBDC trace can be used as well. I need to investigate this option - assuming ODBC is used of course
- Normal audit can be used to indicate tables have been accessed but not the actual SQL (This is better in 10g)
- Fine Grained Audit can be used for selects in 9i and for DML also in 10g
- If a particular table is being monitored - this assumes some knowledge of the SQL anyway then Row Level Security policy function might be used
- It is possible to use various events to get the SQL and predicate used in RLS and hence the SQL sent to the server. Events 10730 and 10060 are two of the possibilities, see my two part paper on Row Level Security for details
- Row level triggers can be used to get the before and after data
As I said, probably there are other ways as well.
