I am having a bit of a nightmare trying to get Hibernate 3 to play nicely with Oracle Clobs. My project just moved up to 3.0 from 2.1.8 where we had solved this problem using the nifty StringClobType posted elsewhere on this website.

The StringClobType doesn't work with 3.0 as the JDBC Connection returned from the preparedStatement in nullSafeSet() is some C3PO proxy connection, not the Oracle connection the method is expecting. This solution relies on being able to call a properietary Oracle connection method to work so no luck there.

I wondered if Hiberante 3.0 had factored the bad driver Oracle behaviour into itself (no good reason to do this) so I tried using a mapping type of 'text'. The stack trace below shows the results.

My question is, is anyone out there succuessfully using Oracle Clobs and Hiberante 3?


Hibernate version:
3.0 rc1

Mapping documents:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="com.real.common.cmsdata.content.Content"
table="CMS.CMS_CONTENT">

<id name="contentId" column="CONTENT_ID">
<generator class="sequence">
<param name="sequence">CMS.CONTENT_ID_SEQ</param>
</generator>
</id>

<property name="createdDate" column="CREATE_DT" not-null="true" />
<property name="expireDate" column="EXPIRE_DT" />
<property name="liveDate" column="LIVE_DT" />
<property name="modifiedDate" column="MODIFIED_DT" />
<property name="remoteId" column="REMOTE_ID" />

<many-to-one name="createdBy" column="CREATED_BY_AGENT_ID" />
<many-to-one name="modifiedBy" column="MODIFIED_BY_AGENT_ID" />
<many-to-one name="dataSource" column="DATA_SOURCE_ID" />
<many-to-one name="contentGroup" column="CONTENT_GROUP_ID" />
<many-to-one name="contentStatus" column="CONTENT_STATUS_ID" />
<many-to-one name="contentType" column="CONTENT_TYPE_ID" />


<!-- Content Content Connection Mapping -->
<set name="contentContentConnections" inverse="true" cascade="all-delete-orphan" outer-join="true">
<key column="CONTENT_ID1"/>
<one-to-many class="com.real.common.cmsdata.content.ContentContentConnection"/>
</set>


<!-- FreeForm -->
<joined-subclass name="com.real.common.cmsdata.editorial.FreeForm" table="CMS.CMS_FREEFORMS">
<key column="CONTENT_ID"/>
<property name="id" />
<property name="title" not-null="true"/>
<property name="columnWidth" column="COLUMN_WIDTH" />

<property name="text" type="text" />

<property name="notes" column="INTERNAL_NOTES" />
</joined-subclass>

</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:
org.hibernate.exception.GenericJDBCException: could not insert: [com.real.common.cmsdata.editorial.FreeForm]
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1839)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2171)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:46)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:136)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:669)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:293)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86)
at com.real.common.cmsdata.editorial.FreeFormTest.doCreate(FreeFormTest.java:73)
at com.real.common.cmsdata.editorial.FreeFormTest.testFreeForm(FreeFormTest.java:43)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.junit2.JUnitStarter.main(JUnitStarter.java:31)
Caused by: java.sql.SQLException: No more data to read from socket
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160)
at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963)
at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:375)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1141)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2149)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2032)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2894)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:98)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1826)
... 30 more


Name and version of the database you are using:
Oracle 8i

The generated SQL (show_sql=true):
Hibernate: select contentsta0_.ID as ID0_, contentsta0_.NAME as NAME61_0_ from CMS.CMS_CONTENT_STATUSES contentsta0_ where contentsta0_.ID=?
Hibernate: select datasource0_.id as id0_, datasource0_.name as name35_0_, datasource0_.description as descript3_35_0_, datasource0_.PAGE_URL as PAGE4_35_0_, datasource0_.SHORT_NAME as SHORT5_35_0_, datasource0_.SHOW_ATTRIBUTION as SHOW6_35_0_, datasource0_.RBN_PATH as RBN7_35_0_, datasource0_.SHOW_ADS as SHOW8_35_0_ from CMS.CMS_DATA_SOURCES datasource0_ where datasource0_.id=?
Hibernate: select contenttyp0_.ID as ID0_, contenttyp0_.NAME as NAME63_0_ from CMS.CMS_CONTENT_TYPES contenttyp0_ where contenttyp0_.ID=?
Hibernate: select user0_.ID as id0_, user0_1_.AGENT_TYPE_ID as AGENT2_16_0_, user0_.USER_TYPE_ID as USER2_17_0_, user0_.GROUP_ID as GROUP3_17_0_, user0_.CREATED_BY as CREATED4_17_0_, user0_.MODIFIED_BY as MODIFIED5_17_0_, user0_.username as username17_0_, user0_.password as password17_0_, user0_.firstname as firstname17_0_, user0_.lastname as lastname17_0_, user0_.email as email17_0_, user0_.PHONE_NUMBER as PHONE11_17_0_, user0_.CREATE_DT as CREATE12_17_0_, user0_.MODIFIED_DT as MODIFIED13_17_0_ from AMS.USERS user0_, AMS.AGENTS user0_1_ where user0_.ID=user0_1_.id and user0_.ID=?
Hibernate: select category0_.id as id0_, category0_.name as name27_0_, category0_.description as descript3_27_0_, category0_.SITE_DIRECTORY_NAME as SITE4_27_0_, category0_.SITE_DIRECTORY_URL as SITE5_27_0_, category0_.CREATE_DT as CREATE6_27_0_, category0_.MODIFIED_DT as MODIFIED7_27_0_, category0_.CREATED_BY_AGENT_ID as CREATED8_27_0_, category0_.MODIFIED_BY_AGENT_ID as MODIFIED9_27_0_, category0_.IMAGE_CONTENT_ID as IMAGE10_27_0_, category0_.INCLUDE_IN_SITE_DIRECTORY as INCLUDE11_27_0_ from CMS.CMS_CATEGORIES category0_ where category0_.id=?
Hibernate: select category0_.id as id0_, category0_.name as name27_0_, category0_.description as descript3_27_0_, category0_.SITE_DIRECTORY_NAME as SITE4_27_0_, category0_.SITE_DIRECTORY_URL as SITE5_27_0_, category0_.CREATE_DT as CREATE6_27_0_, category0_.MODIFIED_DT as MODIFIED7_27_0_, category0_.CREATED_BY_AGENT_ID as CREATED8_27_0_, category0_.MODIFIED_BY_AGENT_ID as MODIFIED9_27_0_, category0_.IMAGE_CONTENT_ID as IMAGE10_27_0_, category0_.INCLUDE_IN_SITE_DIRECTORY as INCLUDE11_27_0_ from CMS.CMS_CATEGORIES category0_ where category0_.id=?
Hibernate: select keywordtyp0_.id as id0_, keywordtyp0_.name as name65_0_ from CMS.CMS_KEYWORD_TYPES keywordtyp0_ where keywordtyp0_.id=?
Hibernate: select keywordtyp0_.id as id0_, keywordtyp0_.name as name65_0_ from CMS.CMS_KEYWORD_TYPES keywordtyp0_ where keywordtyp0_.id=?
Hibernate: select keyword0_.id as id, keyword0_.keyword as keyword64_, keyword0_.DISPLAY_NAME as DISPLAY3_64_, keyword0_.CREATE_DT as CREATE4_64_ from CMS.CMS_KEYWORDS keyword0_ where (keyword0_.keyword=?)
Hibernate: select keyword0_.id as id, keyword0_.keyword as keyword64_, keyword0_.DISPLAY_NAME as DISPLAY3_64_, keyword0_.CREATE_DT as CREATE4_64_ from CMS.CMS_KEYWORDS keyword0_ where (keyword0_.keyword=?)
Hibernate: select keyword0_.id as id, keyword0_.keyword as keyword64_, keyword0_.DISPLAY_NAME as DISPLAY3_64_, keyword0_.CREATE_DT as CREATE4_64_ from CMS.CMS_KEYWORDS keyword0_ where (keyword0_.keyword=?)
Hibernate: select CMS.KEYWORDS_ID_SEQ.nextval from dual
Hibernate: select CMS.CONTENT_ID_SEQ.nextval from dual
Hibernate: insert into CMS.CMS_KEYWORDS (keyword, DISPLAY_NAME, CREATE_DT, id) values (?, ?, ?, ?)
Hibernate: insert into CMS.CMS_CONTENT (CREATE_DT, EXPIRE_DT, LIVE_DT, MODIFIED_DT, REMOTE_ID, CREATED_BY_AGENT_ID, MODIFIED_BY_AGENT_ID, DATA_SOURCE_ID, CONTENT_GROUP_ID, CONTENT_STATUS_ID, CONTENT_TYPE_ID, CONTENT_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into CMS.CMS_FREEFORMS (id, title, COLUMN_WIDTH, text, INTERNAL_NOTES, CONTENT_ID) values (?, ?, ?, ?, ?, ?)

Debug level Hibernate log excerpt:

I haven't had time to check LOBs on H3, but it seams to me that it is the same issue that H2.x has.

I am using UserType from:
http://www.hibernate.org/56.html
check the second solution:
"Updated Clobs handling for Oracle and Hibernate
- uses interceptor and avoids compile-time dependency. "

It has nice trick that gets real connection (not proxy connection), in general it looks like this:



Let us know if it solves your problem.
Lukasz

I changed the version of the C3PO jar (c3p0-0.8.5.jar) from the version which ships w/ Hibernate 3 to the version which shipped w/ Hibernate 2.1.8 (c3p0-0.8.4.5.jar) and the StringClobType worked fine. c3p0-0.8.5.jar seems to return a proxied version of the Oracle JDBC driver whereas its predecessor did not.