Hi all,

I have an application where some of the hibernate entities have string properties larger than 4000 characters. Those properties are mapped with type="text". The hibernate schema generation mechanism craetes columns of type CLOB for those entity properties, which is perfect. The problem is, when I try to execute a HQL query where the CLOB column is in the where clause then the driver throws an Exception saying: "ORA-00932: inconsistent datatypes: expected - got CLOB". The query looks like this:

HQL> from MyEntity where title = :title

After some reading I found out that when I use the to_char() oracle funtion in my query everything works fine:

HQL> from MyEntity where to_char(title) = :title

Unfortunately the to_char() function seems to exist only in the oracle DB. As we also support MS SQL Server as DB system I'm not able to simply change all my HQL queries to use the to_char() function as this will brake the application when it runs with MS SQL Server. I was anyhow very surprised that HQL offers the possibility to use a function which cannot be translated into SQL which then can be run on SQl Server.

Are there any recommendation how this problem can be solved?

I use Hibernate 3.2.2 GA, Oracle 10g, MS SQL Server 2005

Thanks for your help,

Christian.

Hi again,

I have a solution for my problem but I do not really like it and hope to get some comments or alternatives.

I now use to_char() at all places where I comapre a column of type CLOB with a parameter like in this example:

HQL> from MyEntity where to_char(title) = :title

I subclassed the SQL server dialect (org.hibernate.dialect.SQLServerDialect) and registered the to_char() function in the constructor with an empty SQL function:




The result is that hibernate generates SQL where the property is in brackets (e.g. select ... from ... where (entity.title) = ?).

I don't like the solution becauses I made changes to the SQL server dialect instead of changing the Oracle dialect to get the comparission right.

Thanks for any comment.

Christiain.

I've managed to make the HQL query to work by means of the HQL STR() function:

SELECT * FROM Annotation ann
WHERE STR(ann.text) = 'search string'

where Annotation.getText() is annotated with @Lob. Before trying STR(), I too was getting the error at issue.