OracleJsonFunctions.Value string comparison

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
eldar
Posts: 6
Joined: Tue 28 Jul 2015 11:05

OracleJsonFunctions.Value string comparison

Post by eldar » Mon 08 Jan 2018 10:17

When i try to write an entity framework query like below

Code: Select all

context.SomeEntities.Where(t => OracleJsonFunctions.Value(t.Json,"$.path").CompareTo("a") >0)
it is translated to sql query like below

Code: Select all

SELECT ....
WHERE (JSON_VALUE("Extent1"."JSON",'$.path')) > TO_CLOB('a')
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

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

Re: OracleJsonFunctions.Value string comparison

Post by Shalex » Tue 09 Jan 2018 21:18

You are using EF6, aren't you?

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

eldar
Posts: 6
Joined: Tue 28 Jul 2015 11:05

Re: OracleJsonFunctions.Value string comparison

Post by eldar » Wed 10 Jan 2018 05:47

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

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

Re: OracleJsonFunctions.Value string comparison

Post by Shalex » Thu 15 Feb 2018 18:07

  • 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.

eldar
Posts: 6
Joined: Tue 28 Jul 2015 11:05

Re: OracleJsonFunctions.Value string comparison

Post by eldar » Mon 26 Feb 2018 07:32

Thanks, much appriciated.

eldar
Posts: 6
Joined: Tue 28 Jul 2015 11:05

Re: OracleJsonFunctions.Value string comparison

Post by eldar » Mon 26 Feb 2018 12:27

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 :

Code: Select all

select * from table where JSON_VALUE(json_doc,'$.Deadline') > sysdate;
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 :

Code: Select all

create index json_date_index on table (JSON_VALUE(json_doc,'$.Deadline' returning date));
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.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: OracleJsonFunctions.Value string comparison

Post by Pinturiccio » Wed 28 Feb 2018 13:48

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

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

Re: OracleJsonFunctions.Value string comparison

Post by Shalex » Fri 30 Mar 2018 17:29

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

Post Reply