-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 28 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Oracle 9i LOBs -- The Easy Way?
PostPosted: Tue Oct 12, 2004 3:34 pm 
Newbie

Joined: Tue Sep 14, 2004 5:57 pm
Posts: 6
Location: Sunnyvale
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?


Top
 Profile  
 
 Post subject: Oracle LOB support for Java is terrible
PostPosted: Wed Oct 13, 2004 4:28 pm 
Newbie

Joined: Thu Sep 30, 2004 10:31 am
Posts: 4
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


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 13, 2004 4:30 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
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.

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 14, 2004 2:51 pm 
Regular
Regular

Joined: Tue Sep 28, 2004 6:34 pm
Posts: 50
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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 14, 2004 2:52 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
It's a Wiki, just click "Edit".

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 14, 2004 4:08 pm 
Regular
Regular

Joined: Tue Sep 28, 2004 6:34 pm
Posts: 50
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


Top
 Profile  
 
 Post subject: perfect solution
PostPosted: Fri Oct 15, 2004 3:15 pm 
Beginner
Beginner

Joined: Fri Sep 05, 2003 10:17 am
Posts: 42
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.
Code:
-tomcat 4.1.30
-hibernate 2
-Oracle 9i
-Oracle ojdbc14.jar as JDBC driver


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 15, 2004 4:12 pm 
Regular
Regular

Joined: Tue Sep 28, 2004 6:34 pm
Posts: 50
I am glad to hear that, the more people use it the better it gets:)

Lukasz


Top
 Profile  
 
 Post subject: Problem with CLOBs
PostPosted: Tue Jan 04, 2005 6:39 pm 
Newbie

Joined: Tue Jan 04, 2005 4:21 pm
Posts: 4
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!


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 14, 2005 12:45 am 
Regular
Regular

Joined: Tue Sep 28, 2004 6:34 pm
Posts: 50
dboeren did you managed to solve this issue?

a few thoughts:
1. I do not see
Quote:
unsaved-value
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


Top
 Profile  
 
 Post subject: CLOB solution
PostPosted: Fri Jan 14, 2005 10:33 am 
Newbie

Joined: Tue Jan 04, 2005 4:21 pm
Posts: 4
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!


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 14, 2005 10:41 am 
Regular
Regular

Joined: Thu Oct 07, 2004 4:45 pm
Posts: 92
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.


Top
 Profile  
 
 Post subject: Retrieve large string from CLOB column
PostPosted: Mon Mar 14, 2005 12:58 pm 
Newbie

Joined: Mon Mar 14, 2005 12:33 pm
Posts: 6
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:
Code:
<class name="Message" table="MESSAGE">
        <id name="messageid" column="MESSAGEID" type="java.lang.Long">
            <generator class="sequence">
               <param name="sequence">SEQ_MESSAGE</param>
            </generator>
        </id>

        <property name="messagecontent" column="MESSAGECONTENT" type="java.lang.String" />
</class>


Abstract generated class:
Code:
public abstract class AbstractRismessage
    implements Serializable
{
  /** The composite primary key value. */
    private java.lang.Long messageid;

/** The value of the simple xmlmessage property. */
    private java.lang.String messagecontent;


  public java.lang.String getMessagecontent()
    {
        return this.messagecontent;
    }
public void setMessagecontent(java.lang.String content)
    {
        this.messagecontent = content;
    }
}



The hybernate configuration was changed to:
Code:
<property name="hibernate.jdbc.batch_size">0</property>
<property name="hibernate.jdbc.use_streams_for_binary">   true</property>
<property name="SetBigStringTryClob">true</property>

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...


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 17, 2005 12:04 pm 
Beginner
Beginner

Joined: Wed Feb 23, 2005 10:37 am
Posts: 29
Lukasz (Qr):
I tried your solution but i get this exception.

java.sql.BatchUpdateException: ORA-01722:

Is your solution driver version specific?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 17, 2005 3:16 pm 
Regular
Regular

Joined: Tue Sep 28, 2004 6:34 pm
Posts: 50
http://www.techonthenet.com/oracle/errors/ora01722.htm
Quote:
Cause: You executed an SQL statement that tried to convert a string to a number, but it was unsuccessful.

Are you sure that you did setup everything correctly?

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


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 28 posts ]  Go to page 1, 2  Next

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.