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

[anand123]

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.

[MariiaI]

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/ser ... ies004.htm )

[Gruffta]

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]

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

[MariiaI]

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.