ORA-01790 when including entity with limited-length binary field

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
wscalf
Posts: 7
Joined: Wed 12 Dec 2012 00:18

ORA-01790 when including entity with limited-length binary field

Post by wscalf » Thu 18 Jul 2013 18:46

Okay, so I encountered an issue that goes like this..

I have an entity (let's call it 'Root.') This root has many-to-many relationships with two other entities, one of which has a binary field with a max length of 16 bytes. If I query Root and .Include() both of the other entities, I get the error mentioned in the title (ORA-01790: expression must have same datatype as corresponding expression.)

After digging a little deeper, I think I found the problem. The way .Include is implemented, each entity is found, its values are placed in the relevant columns, and NULLs are placed into the columns that represent other entities. These NULL values are cast to match the column types from the actual entities. However, in this case, the cast is incorrect. The column that was generated on my entity with the binary field was RAW(16), but in the separate query segments for pulling values from other related entities (where it should be casting NULL as RAW(16) to match), it's casting a NULL value to BLOB. BLOB is not the same type as RAW(16), so it fails.

If I remove my max length rule, it generates that binary column as BLOB, and then everything works, which is my current workaround. That isn't desirable, though, as I think the BLOB datatype has some negative performance characteristics on small data.

On the other hand, if I hand-edit the generated query to cast as RAW(16) instead of casting to BLOB, that also works without modifying the table. So, if there's a setting or update or something that I can apply so that it works, that'd be preferred.

Any thoughts or advice would be appreciated - thanks!

(Also, a self-contained repro project is available. It's a bit on the large side, though

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: ORA-01790 when including entity with limited-length binary field

Post by Shalex » Tue 23 Jul 2013 15:46

Please check the columns, which participate in a constraint, in storage (SSDL) part of your model - they should be of the same datatype.

If you are using Code-First, set the datatype of the corresponding column in the database via the Column attribute of the class property or via fluent mapping.

If this doesn't help, localize the issue and send us a small test project with the corresponding DDL script of database objects.

tburnette
Posts: 1
Joined: Tue 06 May 2014 20:48

Re: ORA-01790 when including entity with limited-length binary field

Post by tburnette » Tue 06 May 2014 21:08

I am having the same problem... Has there been a resolution to this?

I too have an entity that has many relationships and therefore is generating a pretty lengthy query with a Union All. Each side of union has entities that are completely getting cast as NULL base on their types. The query fails because of the "ORA-01790: expression must have same datatype as corresponding expression" error. As like wscalf, I have tracked it down to how the translation is generated from the following setup.

Oracle Column Datatype : RAW(16)
.NET CSDL definition : Type="RAW" MaxLength="16"
.NET SSDL definition : Type="Binary"

The generated query for the above when doing the NULL casting is TO_BLOB(NULL) ,whereas just like previous stated in the post above, manually changing it to CAST(NULL AS RAW(16)) in the Oracle Client SQL_Worksheet causes the query to execute correctly.

Is there an attribute that I can set in the edmx to force the behavior I am looking for. I have tried to eliminate the MaxLength as described above but that generates an error for that column mapping.

Oracle Database : 11G 64-bit
Entity Framework : 6

Devart Driver versions
Devart Data : 5.0.907.0
Devart Data Oracle : 8.3.115.0
Devart Data Oracle Entity : 8.3.115.6

Any help or guidance would be appreciated.

Thanks,

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: ORA-01790 when including entity with limited-length binary field

Post by MariiaI » Thu 08 May 2014 11:20

tburnette wrote:The query fails because of the "ORA-01790: expression must have same datatype as corresponding expression" error.
...
Oracle Column Datatype : RAW(16)
.NET CSDL definition : Type="RAW" MaxLength="16"
.NET SSDL definition : Type="Binary"
We have reproduced the "ORA-01790: expression must have same datatype as corresponding expression" error with a such scenario. We will investigate it in more details and infrom you about the results as soon as possible.

Post Reply