Devart Forums

When i try to write an entity framework query like below it is translated to sql query like below and yes JSON column is clob column but extracted value from json_value function is not. so oracle gives me "ORA-00932:inconsistant datatypes"
I think there is a logic in sql generation which tries to convert string types each other so comparison should be among same types (clob to clob) but in this case we are not trying to compare column itself but value returned from json_value function.

And some extra, Oracle's JSON_VALUE function has a syntax that can support different data types(number,date,timestamp like) with "RETURNING" clause. OracleJsonFunctions.Value should also support this kind of query generation.

My dotconnect version : 9.5.399.0

You are using EF6, aren't you?

We will investigate the issue and notify you about the result.

Shalex wrote:You are using EF6, aren't you?

We will investigate the issue and notify you about the result.

That is correct. Entity Framework 6.1.3, .net 4.7

• The bug with using the CLOB column as a parameter of the JSON_VALUE Oracle function is fixed
• The new methods OracleJsonFunctions.ValueAs... are added for getting nonstring values returned by the JSON_VALUE Oracle function

Refer to viewtopic.php?f=1&t=36602.

Thanks, much appriciated.

Hi,

We have discovered one more scenario which allows us compare extracted value with another type (timestamp, datetime, number, etc) without specifying returning cluase like below : but with current api we cannot achive this syntax. OracleJsonFunctions.Value function returns string so we can't compare to datetime (that gives compile time error as expected) and also can not use Strings CompareTo method because it allows only primitive types. So adding a third parameter to OracleJsonFunctions ValueAs[TypeName] functions for whether or not generating "returning clause" would be very nice.
And why we wanted to use this syntax is because of Oracle's index generation and usage behaviour.
Oracle allows you to create function based indexes on clob columns like syntax below : but weird part of this oracle strips the "returning date" part from the function and when you try to query table with returning clause oracle doesn't use that index you create and does a full table scan. If you use a syntax like at first example (without returning clause) oracle uses that index. Also specifying hints for forcing to use the index is not helping.

We will investigate the possibility to implement such feature and post here when we get any results.

The new overload of the OracleJsonFunctions.ValueAs... methods is added for turning off generation of the RETURNING clause: viewtopic.php?f=1&t=36966.