Maximum length of SQL in Oracle query

psk_270185 · Post by **psk_270185** » Mon Apr 13, 2009 3:57 am

ERROR:
Src_Scheme_Loans: The provided query statement did not prepare correctly; please verify that your statement is correct;

[
eg.,
select emp.a,emp.b,emp.c from table emp
union
select emp1.a,emp1.b,emp1.c from table1 emp1
]

If the number of columns in the individual select statements exceeds 120 (approx), we are unable to fetch the data.

But the query is working fine on sqlplus and
the job is working fine on IS-8.0 version on Windows

ANy inputs?

Regards,
psk

chulett · Post by **chulett** » Mon Apr 13, 2009 7:40 am

What stage? Sounds somewhat familiar, you might want to contact your official support peoples and see if there is a patch for this issue.

kduke · Post by **kduke** » Mon Apr 13, 2009 10:06 am

Why would you do this? I would combine these outside of SQL in the job.

betterthanever · Post by **betterthanever** » Mon Apr 13, 2009 10:52 am

check the column names you gave in the colums tab.
you are using union..
in the first query the first column is emp.a
in the second query it is emp1.a

use a common column name

like this

select emp.a as a,emp.b as b,emp.c as c from table emp
union
select emp1.a as a,emp1.b as b,emp1.c as c from table1 emp1

where a,b,c are the columns on the columns tab

chulett · Post by **chulett** » Mon Apr 13, 2009 10:57 am

Sorry, but any column aliases ("as c") you use in your sql are ignored by the stage. Not to mention unneeded in the sql as well.

betterthanever · Post by **betterthanever** » Mon Apr 13, 2009 11:14 am

thought diffrent column names on each query...you were right

psk_270185 · Post by **psk_270185** » Tue Apr 14, 2009 1:08 am

hi all,

thanks for the replies. The stage in which the problem comes is teh Oracle Enterprise Stage.

The query actually selects all columns from teh table and also appends a dummy record as shown below.

Select e.ename,e.empno,e.deptno from emp e
union
Select 'a' as ename,1 as empno,'a' as deptno from dual

Even when i dont use alaises for table names, and teh table itself has say around 300 columns, even then we have the problem. Some queries have unions with different tables out of need, and in such cases query length shoots to 700 lines. The jobs thus fails.

Tried removing the aliases..same error. Pls suggest.

Regards,
PSK

Kryt0n · Post by **Kryt0n** » Tue Apr 14, 2009 1:53 am

chulett wrote:Sorry, but any column aliases ("as c") you use in your sql are ignored by the stage. Not to mention unneeded in the sql as well. ...

Are you sure?? We have had jobs fall over because the column name being extracted was different to the stage column name. As soon as we did an "as <stage col name>" it worked fine.

I'm pretty certain Server Edition use to ignore column names but EE does not appear to... certainly isn't for us anyway!

chulett · Post by **chulett** » Tue Apr 14, 2009 8:20 am

Fair enough. My assertion is certainly true for Server jobs, but if that's not the case on the Parallel side of the house, I stand corrected. Thanks.

For what it's worth, only the first sql in a set of "union" sqls would need any column aliases as that one drives the column names in the output set. The rest can be left without them.

psk_270185 · Post by **psk_270185** » Wed Apr 15, 2009 2:53 am

We have tried that.

But there are queries of almost 400 lines (and max is 700 lines). This doesnt seem to work for them.

Thanks,
Shrinivas

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Apr 15, 2009 3:17 am

Can you put together a test of where it breaks ?

chulett · Post by **chulett** » Wed Apr 15, 2009 7:05 am

Or just contact your support. You'll either find out the real maximum or get a patch if this is a bug. Or both.

DSXchange