Is there any limit for query size in OCI stage?

dskid · Post by **dskid** » Fri May 04, 2007 10:24 am

Hello guys,
I am facing some problem related to Oracle stage. In my oracle TARGET stage i am passing a query which i am Selecting and Updating on the same table and that query is too big like it has 1219 characters.
the problem here is if my query is more than 1220 characters its not executing the query and giving fatal that "The provided update statement did not prepare correctly" and update set x.column name = ( se?trim(. and its triming the last part of the query and its not sending the entire query but giving fatal error.
In my job design i am using row generator as source so that i will trigger this query from datastage.

Edwink · Post by **Edwink** » Fri May 04, 2007 12:46 pm

can u able to excute the same query through SQL plus?

dskid · Post by **dskid** » Fri May 04, 2007 3:33 pm

i was able to execute that from SQL plus. but when i try to do that from oracle stage its aborting and i am tryng it from ODBC stage.

ray.wurlod · Post by **ray.wurlod** » Fri May 04, 2007 4:07 pm

Looks like there might be such a limit. It's not documented in the manuals. Ask your support provider, who can ask IBM engineering.

dskid · Post by **dskid** » Sat May 05, 2007 7:01 am

It seems there is a limit for No.of characters and its around 1220 for oracle and 2000 for teradata. i am able to run the same through ODBC stage without any issue.
thanks

chulett · Post by **chulett** » Sat May 05, 2007 7:11 am

Do you know if this limitation is specific to the Oracle Enterprise stage? It must be as I have a couple of my peoples who loves the long user-defined sqls and I haven't run into this issue on the Server side.

I just pulled one example at random and pasted it into Word for some stats:

Characters: 4893
Characters (with spaces): 15705

ray.wurlod · Post by **ray.wurlod** » Sat May 05, 2007 3:30 pm

Are there any line terminator characters in your query?

You have marked the topic as resolved. Please post the resolution.

chulett · Post by **chulett** » Sat May 05, 2007 3:37 pm

Who, me? Tons.

The resolution seems to be to use shorter queries. That or the ODBC stage.

ray.wurlod · Post by **ray.wurlod** » Sat May 05, 2007 5:01 pm

Not you. You're not the OP, so could not have marked the post as resolved.

dskid · Post by **dskid** » Tue May 08, 2007 7:58 am

I resolved this issue by using ODBC stage. when i executed the same query using Oracle enterprise stage its triming the last characters in the query which are more than 1220 and throwing a fatal error that SQL query not prepared properly, when i used the same after removing some fileds(only to reduce the lenght of query) which is less than 1220 then its working fine.