Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: large String properties and Oracle CLOB columns, yet again
PostPosted: Sun Nov 06, 2005 11:17 pm 
Newbie

Joined: Wed Oct 19, 2005 7:15 pm
Posts: 1
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.


Top
 Profile  
 
 Post subject: Oracle Clob 4000 / 4k CLOB limit - solved
PostPosted: Mon Nov 14, 2005 2:55 am 
Newbie

Joined: Mon Nov 14, 2005 2:40 am
Posts: 1
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.