Devart Forums

Posted: **Thu 24 Mar 2011 13:06**

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.

Posted: **Fri 25 Mar 2011 12:46**

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.

Posted: **Mon 04 Apr 2011 16:42**

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

Posted: **Thu 07 Apr 2011 13:09**

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.

Devart Forums

Problem with CAST of CLOB types.

Problem with CAST of CLOB types.