How to log the raw SQL from Oracle occi C++ api?

Question

One of our customers is complaining our application is not working. Their reasoning is that our sql function call to their Oracle database is not getting the "expected" result. Sometime, it should failed but our application get success from their database. It's really frustrating because it's their database and we cannot do any test on it.

We are using the C++ Oracle OCCI API. Is there anyway we can log the raw sql from our end? That will be very helpful and we can ship the script to them and let them debug in their system to figure out the problem.

Thanks in advance.

Answer 1

Ideally you would turn on a trace at the database level which would generate a trace file containing all activity the database performed.

Other alternatives would be to alter you application to log all SQL that it was about to execute against the database.

This post also goes into some other options (they approach it from trying to detect whether SQL injection is happening) to sniff the database activity:

http://www.symantec.com/connect/articles/detecting-sql-injection-oracle

Though it must be setup on the database, a trace will give you the truest results. Oracle Fine Grained Auditing is something else to look into if you are on Oracle 9i or higher.

Answer 2

Depending on the architecture the statements sent across the network do not necessarily mirror the SQLs executed. The obvious example is calling a stored procedure, where the network simply has the call, but the database actions all the underlying SQL of the procedure. However triggers, fine-grained access control, views etc can all have similar impacts.

For the network transfer, you can look at SQL net traces

For the database side, look at DBMS_MONITOR

Answer 3

I assume that you are issuing just a SQL statement, since you say that you want to see the 'raw SQL from your end'. The best thing, then, is to get the database trace, as has been suggested.

What I want to point out is that even if your SQL returns the expected result in a test database, the same SQL may return an unexpected result in another database because the data may be different: the data may be corrupted, indexes may exist or may not exist, constraints may be defined or not, etc. Definitely, you need to get the trace from the database to be able to move forward.