Maximum length of SQL in Oracle query
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 21
- Joined: Wed Mar 12, 2008 4:05 am
Maximum length of SQL in Oracle query
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
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
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
Re: Maximum length of SQL in Oracle query
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
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
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
-
- Participant
- Posts: 21
- Joined: Wed Mar 12, 2008 4:05 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
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
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.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. ...
I'm pretty certain Server Edition use to ignore column names but EE does not appear to... certainly isn't for us anyway!
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 21
- Joined: Wed Mar 12, 2008 4:05 am
Maximum length of SQL in Oracle query
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
But there are queries of almost 400 lines (and max is 700 lines). This doesnt seem to work for them.
Thanks,
Shrinivas
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom