Union query throws error : Failed in 796 ms with error: ORA-01790: expression must have same datatype as corresponding e

Union query throws error : Failed in 796 ms with error: ORA-01790: expression must have same datatype as corresponding e

Postby anand123 » Thu 06 Mar 2014 06:27

recently bought oracle connect profession edition.

when executing an union query in oracle 10g and 11g.

-- Failed in 796 ms with error: ORA-01790: expression must have same datatype as corresponding expression

A first chance exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in mscorlib.dll



checked the sql, there are 2 fields causing the error.

Table 1 Fields
Field 1 Type nvarchar2(32 char)
Field 2 Type clob (ssdl type) (long dbType)

corresponding nullable fields generated for table 2.

Field 1 Type TO_CHAR(NULL)
Field 2 Type TO_CLOB(NULL)

Code: Select all
CREATE TABLE "SYSADM"."TEST1"
( "CLOB_COL" CLOB,
"LONG_COL" LONG,
"VARCHAR_COL" NVARCHAR2(32)
)

SELECT  CLOB_COL, VARCHAR_COL FROM TEST1
UNION
SELECT TO_CLOB(NULL) , TO_CHAR(NULL) AS C2 FROM TEST1


i think, nullable fields generated for the union table, not is matching. any solution to this. If I remove clob, nvarchar2 fields from table 1 then query executed.

thanks
Anand.
anand123
 
Posts: 15
Joined: Thu 30 Jan 2014 08:58

Re: Union query throws error : Failed in 796 ms with error: ORA-01790: expression must have same datatype as corresponding e

Postby MariiaI » Tue 11 Mar 2014 11:02

Please send us a small test project, with which this issue is reproducible, so that we are able to investigate it in more details.
JIC: if you have the LONG datatype in the database table, the query will not work for this case due to the fact that:
The UNION, INTERSECT, and MINUS operators are not valid on LONG columns ( please refer to
http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm )
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Union query throws error : Failed in 796 ms with error: ORA-01790: expression must have same datatype as corresponding e

Postby Gruffta » Thu 20 Mar 2014 09:39

Has there been any progress on this? I to am experiencing the same issue on Oracle 12c.
If you still need a sample project I'd be happy to send one.

Thanks
Gareth
Gruffta
 
Posts: 11
Joined: Tue 11 Aug 2009 14:39

Re: Union query throws error : Failed in 796 ms with error: ORA-01790: expression must have same datatype as corresponding e

Postby Gruffta » Thu 20 Mar 2014 09:52

I have found part of the issue in my case.
I have VARCHAR2 columns in my select query and had not set
Code: Select all
OracleEntityProviderConfig.Instance.CodeFirstOptions.UseNonLobStrings = true;


this caused to_clob to be used instead of to_char when creating the null typed definitions.

When is added the above code the query threw another error this seems to be related to the order of your includes, I had to place the include that had a date column after the include that didn't have any date columns.

After that it all worked, seems brittle though.

Hope it helps.
Gareth
Gruffta
 
Posts: 11
Joined: Tue 11 Aug 2009 14:39

Re: Union query throws error : Failed in 796 ms with error: ORA-01790: expression must have same datatype as corresponding e

Postby MariiaI » Thu 20 Mar 2014 14:06

another error this seems to be related to the order of your includes, I had to place the include that had a date column after the include that didn't have any date columns.

Could you please send us a small test project with the corresponding DDL/DML scripts so that we are to reproduce this issue and investigate it in more details.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for Oracle