I'm back to the problem of Hibernate with large java strings and Oracle CLOBs. I know that http://www.hibernate.org/56.html suggests that it'll "just work" with Hibernate 3.x, Oracle 9i and the Oracle 10g drivers (ojdbc14.jar), provided that the driver property 'SetBigStringTryClob=true' (BTW, I've disabled batching as well with 'batch_size=0', just to be sure). And it does, provided that my string is no more than 4000 characters: that is, with the following mapping and autoschema generation, I can persist a short (<=4000 chars) string attribute to a clob column.

config properties (from the sessionFactory properties, using Spring 1.2.5):

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
...
<property name="hibernateProperties">
<props>
<prop key="hibernate.connection.SetBigStringTryClob">true</prop>
...


mapping:

<class name="AuditTrace" table="audit_trace">
....
<property name="preContext" type="text"/>
<property name="postContext" type="text"/>
</class>

When the string is longer than 4000 characters, though, I get the following exception:

org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not insert: [com.telstra.imode.common.audit.AuditTrace]; uncategorized SQLException for SQL [insert into audit_trace (initiatorType, initiator, userId, suid, msisdn, providerId, serviceId, category, action, summary, timestamp, preContext, postContext, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; SQL state [72000]; error code [1483]; ORA-01483: invalid length for DATE or NUMBER bind variable
; nested exception is java.sql.SQLException: ORA-01483: invalid length for DATE or NUMBER bind variable

java.sql.SQLException: ORA-01483: invalid length for DATE or NUMBER bind variable

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1144)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2152)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2035)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2876)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:609)
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1853)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2200)
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:730)
at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:394)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:359)
at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:595)
at com.telstra.imode.common.audit.persistence.AuditDaoImpl.store(AuditDaoImpl.java:18)
at com.telstra.imode.common.audit.persistence.DatabaseAuditor.audit(DatabaseAuditor.java:39)
at com.telstra.imode.common.audit.DatabaseAuditorTest.test_longContextString(DatabaseAuditorTest.java:101)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)

I'm wondering if the auto-schema generation has some default storage parameters for the clob columns? When I browse the database with a client (DbVisualizer), the table metadata suggests that the 'column_size' of the CLOB columns is 4000----even though I haven't specified that anywhere.
I've tried altering the table by adding LOB storage parameters for the columns, but without success.

Any pointers are appreciated.

I've found a solution to this problem. The key is to use the Oracle 10g Release 2 ojdbc14 driver. With this you have a thin driver that can store much more than 4k (and I have tested this). You do not need to set the SetBigStringTryClob property, also the driver is backwards compatible to at least oracle 9i (the database version that I tested it on).

Use the normal jdbc thin connection url as follows:

jdbc.url=jdbc:oracle:thin:@servername:1521:database

BTW: I found this information after looking at this site, so big thanks to these guys:

http://opensource2.atlassian.com/conflu ... OB+or+CLOB

You can download the driver from oracle (make sure you get release 2) - somewhere on this site:

http://www.oracle.com

And here are documented Oracle datatype limits:

http://www.ss64.com/orasyntax/datatypes.html