Problem with CAST of CLOB types.

[mas]

I have a table, Metadata, with the columns:

Name: VARCHAR2(50)
Value: CLOB

I try to execute the following Entity SQL query, where @p is an integer:

Code: Select all

SELECT VALUE m FROM Metadata AS m WHERE m.Name = 'NumberValue' AND CAST(m.Value as System.Int32) > @p

However, I get the exception with the message: ORA-00932: inconsistent datatypes: expected NUMBER got CLOB

I looked into dbMonitor, and I found that the resulting Oracle query was:

Code: Select all

SELECT 
"Extent1".WF_INSTANCE_ID AS WF_INSTANCE_ID, 
"Extent1".NAME AS NAME, 
"Extent1".VALUE AS VALUE, 
"Extent1".SERIALIZED_VALUE AS SERIALIZED_VALUE
FROM METADATA "Extent1"
WHERE ("Extent1".NAME = 'NumberValue') AND ((CAST("Extent1".VALUE AS NUMBER)) > :p)

The problem is that a CLOB cannot be casted to NUMBER. It must be first casted to VARCHAR then to NUMBER. Following this logic, I tried the following Entity SQL query:

Code: Select all

SELECT VALUE m FROM Metadata AS m WHERE m.Name = 'NumberValue' AND CAST(CAST(m.Value as System.String) as System.Int32) > @p

Which was successful, and resulted in the following Oracle query:

Code: Select all

SELECT 
"Extent1".WF_INSTANCE_ID AS WF_INSTANCE_ID, 
"Extent1".NAME AS NAME, 
"Extent1".VALUE AS VALUE, 
"Extent1".SERIALIZED_VALUE AS SERIALIZED_VALUE
FROM METADATA "Extent1"
WHERE ("Extent1".NAME = 'NumberValue') AND ((CAST( TO_CHAR("Extent1".VALUE) AS NUMBER)) > :p)

I get a similar error message when I try to CAST a CLOB to System.DateTime and System.DateTimeOffset.

I am using dotConnect for Oracle Professional 6.10.126.0.

[AndreyR]

Thank you for the report and for sharing the workaround. We are investigating the issue, I will let you know about the results of our investigation.

[AndreyR]

This problem is fixed n the upcoming build.
We plan to release the build this week.

[AndreyR]

The new build of dotConnect for Oracle 6.10.135 is available for download now. This build fixes the CAST problem.
It can be downloaded from Download Page (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to this post.