Hi, we are having the following problem: there's a query generated by ODAC1120250 32 bits (October 2011) that works fine in Oracle 11g Express, but in Oracle 11.1 generates the following error: "ORA-01790: expression must have same datatype as corresponding expression".
We isolated the problematic query (debugging and using .ToTraceString()) and, when analyzing it, we saw that there are various UNION ALL. We guess that the error could come from data types not matching exactly, but we haven't confirmed it yet.
The query itself weights 20 Kb, so I can't post it here. Please tell me if we can send it via email.
Thanks and regards,
PS: Technical data: Development Environment
Visual Studio .Net 2010 SP1
.Net Framework 4
ODAC1120250 32 bits (October 2011)
Windows 7 32 bits Testing environment (works ok)
Windows Server 2008 R2 (English)
Oracle Database 11g Express Edition Release 18.104.22.168.0 - Beta (32 bits) Production environment (generates the error)
Windows 2003 R2 32 bits SP2 (Spanish)
Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - Production (32 bits)
Got it. Thanks.
Is there any difference in the generated queries when use Oracle 11g Express (126.96.36.199) and Oracle 188.8.131.52.0?
Would you please also provide LINQ query and script to create test tables?
Hi, we haven't debugged the application or traced the query against the production server (it's in our customer's office) buy I think there's no difference since the query itself has been tested using sqldeveloper against both servers (XE 11.2 and Enterprise 11.1) and the behavior is consistent. By consistent I mean:
- The query executed via sqldeveloper against Oracle XE 11.2 runs ok and so does the application.
- The query executed via sqldeveloper against Oracle Enterprise 11.1 fails with an ORA-01790 error, and so does the application (exceptions are being logged in the Event Log).
To sum up: the same query runs ok in Oracle XE 11.2 and fails in Oracle Enterprise 11.1.
Additional information: we have tried explicit casting the NULL literals to the corresponding data types (in the UNION ALLs) and the query works fine in both versions. ¿Is there an option in ODAC to tell the query generator to write this explicit castings?
Regarding the DB schema and the LINQ query, I'll be sending them asap.
It looks like this is 11.1 issue and it has been fixed in 11.2.
There is no option in ODAC to tell the query generator to write explicit castings.
It's driven by the command tree, which is from the LINQ query.
Would you please tell us more about the following? An example would help a lot.
"we have tried explicit casting the NULL literals to the corresponding data types (in the UNION ALLs)
and the query works fine in both versions."
I think you meant those NULL columns in the SELECT list, such as
NULL AS "C2",
So you did
CAST(NULL as <datatype>) as C2,
Edited by: shsu on Dec 28, 2011 7:22 PM
Correct. Using TO_NUMBER(NULL) worked as well, and it was only in certain numeric fields (we isolated the four of them) in the query.
Yesterday our customer finished installing the 11.2 version in their production environment and fortunately the problem was solved.
I don't think this thread ever concluded what the exact bug number was. The problem appears specific to the DB server, not ODP.NET. Certainly, the guaranteed solution is to use DB 11.2.
If your application is not prepared to move to 11.2 server, you may want to find what this particular bug is to see if it's been fixed in 10.2. Or you can upgrade to the latest Oracle 10.2 patch if that's an easier way to verify. It would be helpful to know what the bug number is so that if patching doesn't solve the issue, you'll know what bug fix to ask Oracle to backport to 10.2.