Problem with CAST of CLOB types.

Problem with CAST of CLOB types.

Postby mas » 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.
mas
 
Posts: 25
Joined: Mon 15 Nov 2010 12:09

Postby AndreyR » 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » Mon 04 Apr 2011 16:42

This problem is fixed n the upcoming build.
We plan to release the build this week.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle