Hibernate version: 2.1.4

Name and version of the database you are using: Oracle 9i (9.2.2)

I've used Hibernate succesfully to deal with LOBs (BLOBs and CLOBs) using a DB2 database. Now I'm trying to use the same code with an Oracle 9i database. The only problem with the migration has been LOBs. Everything else has gone very smoothly, which is a great testimonial to Hibernate. So here's what's going on with the LOBs.

For dealing with LOBs on DB2, I had used the design patterns mentioned in the documentation (http://www.hibernate.org/73.html -- Blob to byte[] and http://www.hibernate.org/76.html == Clob to String.) These both worked on Oracle as well, but only for objects of size < 4K. As soon as I went over the 4K limit, I immediately got an error about stream types not being allowed for batch updates. So of course I searched the FAQs and message boards and found this: http://forum.hibernate.org/viewtopic.ph ... racle+blob.

Now I immediately had a few issues with this solution of turning off batch updates. First, doesn't this obviously cause a performance hit, potentially a pretty significant one? Personally I use a cascading with Hibernate, because I think it makes application code for dealing with highly nested structures much simpler. It's one of my favorite things about Hibernate. So in particular it seems pretty horrible to turn off batching. Maybe my use isn't typical, or maybe the performance hit isn't as bad as it would seem.
At any rate, I guessed that the root of the problem is that Oracle does not allow LOBs to be set in insert statements (or maybe it's not multiple LOBs?) So a seperate insert/update is issued and Hibernate batches them together. If Oracle is not going to allow the batching, shouldn't the Dialect/Configuration/SessionFactory know this and not try to do it? Is there something more going on here that I'm not aware of?

Anyways, I turned off batching and immediately got an error about "connection reset by peer" i.e. my connection closed during the middle of the transaction. I correctly guessed that this was a crash on Oracle, and found the corresponding dump file. Then I (finally) read this: http://www.hibernate.org/56.html. The pattern itself is very useful, but of course it's not really usable as it creates a memory leak. The many responses provide some great information. They indicate that not only must one write a very Oracle specific UserType, but must also write an Oracle specific Interceptor to take care of the memory leak.

So is this really the only way to handle LOBs with Oracle? After reading all this, my conclusion would be that Oracle LOBs should not be used with Hibernate, as most Hibernate advantages are lost. At the very least a hybrid approach is needed where only the non-LOB fields are mapped using Hibernate and JDBC is used for the LOB fields. It would seem that would involve the same or maybe even less Oracle specific code, and at least it would all be in the same place making it less buggy. Maybe I'm being too harsh, or maybe there is an easier way? Again, should this be stuff handled by the Dialect? Isn't that (one of) the point of a Dialect, that it handle vendor specific special needs?

I don't think this is really a problem with Hibernate. Oracle's thin driver is very buggy with respect to LOBs. We have the same problem at my company, and we don't currently use Hibernate. We are currently evaluating other JDBC drivers to try and resolve the problem (Oracle's OCI driver, and the Oracle driver from DataDirect).

--Steven

Yes, try the DataDirect drivers. While they should be avoided for obvious reasons (these clowns are responsible for the last 20 years of SQL standard suffering), they have a pretty good Oracle driver. That is, compared to the POS that Oracle delivers.

I guess it might be a good time to update all faqs related to Oracle and LOBS. Especially that many people have to go through UserType-OracleLob creation. Also because it is error prone - if someone forgets to free the temporary lob (interceptor solution).
Even better idea would be to integrate "one true solution" as hibernate

It's a Wiki, just click "Edit".

I have just added the solution "Updated Clobs handling for Oracle and Hibernate" to:
http://www.hibernate.org/56.html

It works for me Oracle 8 and 9 + Oracle 9 drivers

Just copy/paste and have fun.

Lukasz

I used this code pretty much right out of the box and it worked perfect .. so far. I have say thank you guys for running into and solving this problem before i got to it :-)

thanks for the fix.

some facts on my setup.

I am glad to hear that, the more people use it the better it gets:)

Lukasz

We are using Lukasz's "Updated Clobs handling for Oracle and Hibernate" solution and experiencing a problem.

The table which contains the CLOB is working fine. However, we are using this table cascaded from another table and it randomly fails to insert to the parent table.

Let me illustrate what I mean...
<hibernate-mapping package="pallas.crossroads.data.bean">
<class name="MessageQueue" table="MESSAGE_QUEUE">
<many-to-one
name="message"
class="ClobData"
not-null="false"
cascade="all"
>
<column name="MESSAGE"/>
</many-to-one>
</class>

<hibernate-mapping package="pallas.crossroads.data.bean">
<class name="ClobData" table="CLOB_DATA">
<id
column="CLOB_DATA_ID"
name="id"
type="integer"
>
<generator class="sequence">
<param name="sequence">CLOB_DATA_ID</param>
</generator>
</id>
<property
column="CLOB_DATA"
name="clobData"
not-null="true"
type="com.pallas.hibernate.clob.OracleClobType"
/>
</class>
</hibernate-mapping>

So, a MessageQueue object has a field which refers to a ClobData object, with a cascade type of "all". (There are actually a couple more fields in MessageQueue, deleted for clarity, but only strings and integers).

I execute the following code:
tx = hibernateSession.beginTransaction();
MessageQueue hibernateMessageQueue = new MessageQueue();
ClobData messageClob = new ClobData();
messageClob.setClobData(message);
hibernateMessageQueue.setMessage(messageClob);
MessageQueueDAO dao = MessageQueueDAO.getInstance();
dao.save(hibernateMessageQueue, hibernateSession);
tx.commit();

So, this should insert two records, ClobData (cascaded) and MessageQueue. ClobData never fails to insert. However, the MessageQueue record seems to succeed sometimes and fail at other times. This is true even when the actual message string is identical between runs and definately much less than the 4000 character limit.

Do you know what might be causing this problem? If I change the line
hibernateMessageQueue.setMessage(messageClob);
to
hibernateMessageQueue.setMessage(null);
then the MessageQueue will always save correctly to the database.
This leads me to believe that it is some interaction of the cascading with your CLOB solution that might be causing the difficulty.

Thanks for any assistance that anyone can offer regarding this problem!

dboeren did you managed to solve this issue?

a few thoughts:
1. I do not see for the <id> element.
2. change clobData property to String (in other words eliminate CLOB) and see if problem persists, setting to null doesn't check cascade at all.
Also I want to make sure where to look - cascade, clobs, or somewhere else:)
3. do you have particular case when this happens?

Lukasz

It turns out that the problem was our own, and did not have any relation to your CLOB solution. It was a deployment issue which caused two copies of our table-watching process to be executing. Thus, there was a race condition where when a record appeared both copies attempted to process it. I was only watching one (unaware of the other) and since it only was getting half the records it appeared they were failing to insert. The reason they seemed to be partially inserting was a bad cascade setting. We had save-update, when it should have been all because we were issuing a delete on the hibernate object after processing.

Thanks for your response, and for all your work in developing the CLOB workaround in the first place!

FWIW, we are using CLOBs successfully with the Oracle 10 driver supplied by Oracle. It works fine on Oracle 9i databases, too. No special UserType required. We just set the property types to "text". Although we do have to disable batching.

I read this forum about clob's and Oracle and was amazed when I read that using the OCI driver and the new oracle 10 driver (ojdbc14.jar) would make your life easy. So I changed my configuration and was amazed to see I could easily store a large XML message as a string into my CLOB column.
But then the next step is retrieving that column again. Unfortunately I do not seem to get that working.
The column is mapped to a java.lang.String, as indicated in this forum. That works for storing the column, but when the object is retrieved, I keep getting a null value, although the database shows the value is not null!

My mapping:


Abstract generated class:



The hybernate configuration was changed to:

And I am using the OCI driver.

So using the the setMessagecontent method works like a dream after changing my hybernate configuration. But using the getMessagecontent method always returns a null.

Does anyone have an idea? I'd rather not implement the Interceptor stuff if I don't need to.

Thanks for any suggestions...

Lukasz (Qr):
I tried your solution but i get this exception.

java.sql.BatchUpdateException: ORA-01722:

Is your solution driver version specific?

http://www.techonthenet.com/oracle/errors/ora01722.htm

Are you sure that you did setup everything correctly?

Remove lob from hibernate's mapping and check if it works fine.