Hibernate+Oracle+Clob: Content of attributes get switched.

Question

Has anyone heard of this error before:

I have a Java 5 application, that uses Hibernate 3.3.2 to access an Oracle Database 10g, the JDBC driver is the Oracle light driver version 10.2.0.4.0 (the most recent version, I think). The database access happens during a transaction that is managed via the AOP-tools of spring. There is one database table mapped to one Java entity, the mapping is done via annotations. There are two attributes in this entity that are CLOB in the database. They are annotated according to JPA with "Lob" and are Java Strings. Everthing works fine unless both attributes have values with more than 4000 characters: After the commit the values are switched on the database, that is attribute A contains the value of attribute B and vice versa.

This is not a joke!

The Eclipse debugger tells me that the Java entity has the correct values until the transaktion closes (I did not debug into the spring transaction handling). This error does not occur if both Clobs are annotated with the Hibernate annotation 'Type(type = "clob")' (in which case they have to be of the type java.sql.Clob and not String, of course).

Answer 1

I'm not 100% sure but could you try to set the connection property SetBigStringTryClob to true (see How To Handle CLOBs Easily in JDBC?).

If you are using Spring, something like this:

<property name="hibernateProperties">
  <props>
    <prop key="hibernate.connection.SetBigStringTryClob">true</prop>
  </props>
</property>

Answer 2

Meanwhile I found a solution to the problem: The SQL-dialect was wrong, someone set it to Oracle9 instead of the correct version, which is this:

name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"

Setting the correct SQL-dialect solves the problem :-)