Why would OraOLEDB.Oracle GetRecordSet() throw "ORA-00907: missing right parenthesis" error?

Question

I am unfortunately working on an old VB6 app that is constructing a huge (11902 characters) ad-hoc SQL statement which is then passed to the GetRecordSet() method of my DBConnection. The app is using the OraOLEDB.Oracle provider. When the GetRecordSet() method is executed, however, I get the following error:

ORA-00907: missing right parenthesis

Now I have grabbed the contents of the string that is passed in to the GetRecordSet() function and have thoroughly examined it and there are no missing parens. Additionally, I am able to execute the select statement in Oracle SQL Developer as well as SQL*Plus, withou difficulty. So I am sure that the string is syntactically correct.

I am beginning to suspect that something fishy is happening in the provider - perhaps the string is too long and it is getting truncated somewhere?

Can anyone out there help me out?

Thanks, Steve

Answer 1

Check the oracle trace file. First set your session in trace mode: Alter session set sql_trace = true;

And run your query in the same session. You can find the trace file on the database server in the directory pointed to by the user_dump_dest.

If the query is completely in the trace file, it has been completely received by the database.