Hibernate > CLOB > Oracle :(

Asked 3/12, 2009 at 22:34 Answered 25/1, 2010 at 7:13

Solved java oracle hibernate oracle9i clob

I am trying to write to an Oracle clob field a value over 4000 characters. This seams to be a common issue but non of the solutions seem to work. So I pray for help from here.

Down and dirty info:
Using Oracle 9.2.0.8.0
Hibernate3 implementing pojo's with annotations
Tomcat 6.0.16
Oracle 10.2.x drivers
C3P0 connction pool provider

In my persistence.xml I have:

<persistence-unit name="DWEB" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <properties>
        <property name="hibernate.archive.autodetection" value="class"/> 
        <property name="hibernate.connection.password" value="###" />
        <property name="hibernate.connection.username" value="###" />
        <property name="hibernate.default_schema" value="schema" />
        <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />
        <property name="hibernate.c3p0.min_size" value="5" />
        <property name="hibernate.c3p0.max_size" value="20" />
        <property name="hibernate.c3p0.timeout" value="300" />
        <property name="hibernate.c3p0.max_statements" value="50" />
        <property name="hibernate.c3p0.idle_test_period" value="3000" />
        <property name="show_sql" value="true" />
        <property name="format_sql" value="true" />
        <property name="use_sql_comments" value="true" />
        <property name="SetBigStringTryClob" value="true"/>
        <property name="hibernate.jdbc.batch_size" value="0"/>
        <property name="hibernate.connection.url" value="jdbc:oracle:thin:@server.ss.com:1521:DDD"/>
        <property name="hibernate.connection.driver_class" value="oracle.jdbc.driver.OracleDriver"/>
    </properties>
</persistence-unit>

The getter and setter looks like:

@Lob 
@Column(name="COMMENT_DOC")
public String getDocument(){
    return get("Document");
}
public void setDocument(String s){
    put("Document",s);
}

The exception I am getting is:

SEVERE: Servlet.service() for servlet SW threw exception
java.sql.SQLException: Io exception: Software caused connection abort: socket write error
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334)
    at oracle.jdbc.ttc7.TTC7Protocol.handleIOException(TTC7Protocol.java:3678)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1999)
    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:46)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2275)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2688)
    at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
    at org.hibernate.ejb.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:304)
    at org.sw.website.actions.content.AddComment.performAction(AddComment.java:60)
...

If I need to give more info pleas ask. Everything works until the dreaded limit is exceeded.

Giliana answered 3/12, 2009 at 22:34 Comment(3)

the exception doesn't seem related to the CLOB. Does the exception occur when you remove the CLOB? – Thermodynamic 4/12, 2009 at 7:4

Yes, and it only occurs if the size is over 4000 – Giliana 4/12, 2009 at 14:34

This morning the exception is different. It is the more often reported java.sql.SQLException: No more data to read from socket at oracle.jdbc.dbaccess ... – Giliana 4/12, 2009 at 14:47

Thanks to non sequitor for all the help. I have this working and figure I will put all the pieces here for future reference. Regardless of all the claims about upgrading the drivers and everything would work, non of that worked for me. In the end I had to implement a 'org.hibernate.usertype.UserType' I named it the same as all the examples on the web StringClobType. Save for some imports I used the example from Using Clobs/Blobs with Oracle and Hibernate. As far as I am concerned ignore the "beware" claim.

There was one change I had to make to get merges to work. Some of the methods were not implemented in the provided code sample. Eclipse fixed it for me by stubbing them out. Cool, but the replace method needs to be actually implemented or all merges will overwrite the data with a null. Here is my implementation:

public Object replace(Object newValue, Object existingValue, Object arg2)throws HibernateException {
    return newValue;
}

I will not duplicate the class implementation here go to the above link to see it. I used the code in the third gray box. Then at the top of the pojo class I wanted to use it in I added the following after the imports

...  
import org.hibernate.annotations.Type;  
import org.hibernate.annotations.TypeDefs;  
import org.hibernate.annotations.TypeDef;  

@TypeDefs({  
    @TypeDef(  
        name="clob",  
        typeClass = foo.StringClobType.class  
    )  
})  
@Entity  
@Table(name="EA_COMMENTS")  
public class Comment extends SWDataObject implements JSONString, Serializable {  
...  
}

Then to use the new UserType I added the annotation to my getter:

@Type(type="clob")
@Column(name="COMMENT_DOC")
public String getDocument(){
    return get("Document");
}

I did not need the @Lob annotation.
In my persistence.xml the persistence-unit declaration ended looking like:

<persistence-unit name="###" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <properties>
        <property name="hibernate.archive.autodetection" value="class"/> 
        <property name="hibernate.connection.password" value="###" />
        <property name="hibernate.connection.username" value="###" />
        <property name="hibernate.connection.url" value="jdbc:oracle:thin:@server.something.com:1521:###"/>
        <property name="hibernate.connection.driver_class" value="oracle.jdbc.OracleDriver"/>
        <property name="hibernate.default_schema" value="###" />
        <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9iDialect" />
        <property name="hibernate.c3p0.min_size" value="5" />
        <property name="hibernate.c3p0.max_size" value="100" />
        <property name="hibernate.c3p0.timeout" value="300" />
        <property name="hibernate.c3p0.max_statements" value="50" />
        <property name="hibernate.c3p0.idle_test period" value="3000" />
        <property name="hibernate.c3p0.idle_connection_test_period" value="300" />
        <property name="show_sql" value="false" />
        <property name="format_sql" value="false" />
        <property name="use_sql_comments" value="false" />
        <property name="hibernate.jdbc.batch_size" value="0"/>
    </properties>
</persistence-unit>

The SetBigStringTryClob never worked for me and was not needed for this final implementation.

My lesson learned is in the end it is probably better to join then to fight. It would of saved me three days.

Giliana answered 7/12, 2009 at 20:52 Comment(2)

Looks like the original post is gone, only way I could find it was using the archives: web.archive.org/web/20090322003916/http://www.hibernate.org/…? – Jeffers 5/7, 2011 at 18:2

Yeah you should post the code rather than rely on the link, the original is gone. – Aaberg 23/1, 2015 at 0:25

I think your problem might be that you are using Oracle 9i but Hibernate dialect is 10g. Make sure your driver,db version and dialect are all in sync because there is a 9i dialect as well org.hibernate.dialect.Oracle9iDialect

Anasarca answered 4/12, 2009 at 20:15 Comment(12)

Thanks, started with the 9i but to no avail. I went back and set it to 9i again to double check. Still getting the same exception. – Giliana 4/12, 2009 at 20:39

What about the drivers are u still using drivers for 10g when u are using 9i? Also can you post the sql generated? – Anasarca 4/12, 2009 at 20:55

How can you tell which drivers are being used? I am using the latest version of ojbc14.jar from the oracle site. The driver class in the persitence.xml is oracle.jdbc.driver.OracleDriver . I'll dig out the sql being run and post it. – Giliana 4/12, 2009 at 21:6

This is the sql generated by hibernate: insert into pweb60.EA_COMMENTS (APPROVED, ARCHIVED, PATH, DELETED, COMMENT_DOC, FINISHED, MODERATED_BY, PARENT_COMMENT, TOPIC_ID, USER_ID, ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) – Giliana 4/12, 2009 at 21:38

According to you, you are using "Oracle 10.2.x drivers" I'm not sure exactly what that is – Anasarca 4/12, 2009 at 21:52

So I did, sorry about the confusion. I just downloaded the Oracle 9.2.0.1 drivers. Still the same issue. Sure had my hopes up for a second. – Giliana 4/12, 2009 at 22:3

This issue seems to be a recurring one in Hibernate and I can't believe it still exists on the Hibernate forum a users have created/extended a UserType. For myself I have used Spring and typeDef-ed my clob type like

@org.hibernate.annotations.TypeDef( name="clob", typeClass=org.springframework.orm.hibernate3.support.ClobStringType.class )

this has worked fine for me on Oracle.Even if you are not using Spring you can look at the ClobStringType source, write ur own class and use it similarly on getDocument() like this @org.hibernate.annotations.Type(type="clob") – Anasarca 4/12, 2009 at 22:8

I have seen a lot of that and they are all dated around 2004. I have been trying to avoid a "hack" solution. Others claim they change the driver and all works well for them. Which leads me to believe I have a config issue either in my code or possibly the way the DBA set up the DB. – Giliana 4/12, 2009 at 22:40

Mark why don't you reload your Tomcat now that all your drivers,dialect etc are straight and let me know – Anasarca 4/12, 2009 at 22:52

Connection.setBigStringTryClob is a 10g feature I believe, try removing it from your persistence.xml and then also verify that column type for comment_doc in the db is clob. – Anasarca 4/12, 2009 at 23:6

Did all that and verified per your instructions with little change. With out the setBigStringTryClob I get a No more data to read from socket exception. Adding it back I started talking with our DBA and each time I run the query there is a protocol violation error dump on the DB server – Giliana 7/12, 2009 at 14:52

Thanks, for the help, I had to implement the user type and now it works. The man finally beat me down. – Giliana 7/12, 2009 at 20:28

It should be:

<property name="hibernate.connection.SetBigStringTryClob">true</property>
<property name="hibernate.jdbc.batch_size">0</property>

And not:

<property name="SetBigStringTryClob">true</property>

And use the right dialect for your database (org.hibernate.dialect.Oracle9iDialect).

Also make sure that you are using the latest Oracle 10g Release 2 thin driver (10.2.0.4) or later.

Whirligig answered 25/1, 2010 at 7:13 Comment(0)

We had a similar problem in the past, with LONG columns instead of CLOBs. The problem was the JDBC driver, the one we use now and works fine is

Synergistic answered 4/12, 2009 at 16:25 Comment(1)

Thats the driver jar we are using. – Giliana 4/12, 2009 at 20:34

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags