Sending Long Strings to Oracle

[Bonediggler1]

Hi-

I have a chunk of code that defines the SQL statement to be used in a pass through query to an Oracle DB. The SQL is broken up into 2 parts: the first part is very long and static, and the second part changes each time the DB is run. I use VBA to concatenate the 2 parts.

The problem is that when feeding the string to the pass through query, I get "Syntax error, missing operator".

When I get the string from the immediate window and then paste into Toad for Oracle, it seems as though access is breaking up the string in the middle of words. When I correct this in Toad the query runs fine...so it is not a syntax error on my part.

For example the string is something like this (only much longer):

"SELECT BLAH1 " & _
"BLAH2, BLAH3, BLAH4 " & _
"BLAH5 FROM " & _
"TABLE1 WHERE BLAH"

When taking this from the Immediate Window it is more like:

"SELECT BLAH1, BLAH2, BLAH3, BLA
H4, BLAH5 FROM TABLE1 WHERE BLAH"

...which I think is causing the error.

Any suggestions?

Thank you!!!

 

[lameid]

When you copy a string form the immediate window and it wraps, when you paste it it insterts a carriage return / line feed where it wraps. I never thought to complain about... wordpad does the same thing but not notepad.

Are you setting the SQL property of a querydef object? It should work despite the quirkiness of the immediate window.

 

[Bonediggler1]

Yes, I am setting the SQL property of a Querydef to equal this string.

I have used this technique many times before (though not with strings quite as large) and it always works.

 

[lameid]

You might see if there is a limitation with the ODBC driver.... Or first update to the latest one.

Your example is relatively short. I can't imagine it would have a problem.

It seems to me the problem is on the oracle side. I have never used Oracle but there should be some sort of SQL statement tracing tool so you can see what is beeing sent to the server.

 

[Bonediggler1]

Hmmm

This is being evaluated by Access before being passed to Oracle. The error occurs like this:

qdf2.sql = fSQL(clInv)

Where fSQL is a function that builds the string. After the string is built in the function and passed back to be assigned to qdf2, the error is thrown...

 

[lameid]

What does this in the immediate window return?

? Len(fSQL(clInv))

 

[Bonediggler1]

5009

 

[lameid]

That is well within the limits a query supports.

Are you sure you don't have a syntax error with something that long?

After the code runs, switch to the sql view of the query and copy the SQL in to the Oracle tool (TOAD?) and see if it runs. If not, find your syntax error...

 

[Bonediggler1]

I'm an idiot.

I was assigning the SQL to the wrong QueryDef. I was assinging it to a make table query when I thought it was for the pass through...no wonder it didn't work!

But I learned a lot along the way, at least.

Thanks for taking the time to look into this!