Hello!

I have take a look into the handling of clobs in oracle 9 and found the following:

If you do have a clob in the database, and use setString on the JDBC-Statement it will work as long as the string is less than 4000 chars.
The database automatically wraps it in an clob.
setString fails only if you use a string longer than that.

The oracle jdbc driver force us to use the clob stuff (a shame)

However, i thought:
If one use setString with an longer string - the field in the database must be a clob - and so i could do this transparently by wrapping the whole oracle driver and override the setString function.
getString in the ResultSet uses the ResultSetMetadata to decide if getClob should be used instead.

In the case of setString - I use the createTemporary function and store a reference to the clob. If the statement gets closed, i will free them.
So no need to have a listener or any special string-type in hibernate anymore.
It is completely transparent.

For sure, this is currently not in use in an real-live application and i might have overseen something, but give it a try:
http://www.l3x.net/~im/JdbcOraWrapper.zip

Inside the zip you will find a jar (the precompiled oracle wrapper) and the source for all this.

Simply place the JdbcOraWrapper.jar and the oracle jdbc driver in your classpath.
Use "JdbcOraWrapperDriver" as your new Oracle driver and change the
url to jdbc:oracle_clob:thin:.....

(ok - this is not "transparent" but allows you to use both drivers at the same time :-)

Hi!

Thanks for your Oracle 9 tip...We could make it work with Oracle 10g but we don't know (since you said that your approach wasn't used in production yet) if you are aware about memory leaks related to "createTemporay" CLOBs...(if you code already handle this issue)

See
http://forum.hibernate.org/viewtopic.php?t=931155&highlight=clob

If not - we think the "ultimate" approach would be to implement the "releaser" (from Paul) into your "wrapper" class!

What do you think? We could help you or implement this but we'd like to have your opinion about it...

Thanks again,

Paulo

Currently we use it on our wiki server.
I havent noticed a memory leak now, but this might lead to the low usage of this server.

Why do the wrapper need a Releaser thread?

If one calls PerparedStatement.close() or ResultSet.close() the clobs are already released by calling Clob.freeTemporary(), so there should be no memory leak.

Or do i misunderstand the problem?

Hi!

I really don't know the reasons of possible memory leaks...You can get better explanation in the link above... I'm just re-checking with you because your approach was the only one that solve our problem (we've tried about 5!)

Thank you!

Paulo

I am happy to see it works for you.

I read the other post, but come to the conclusion there is no need for such a Releaser thread in my wrapper - you simply have to ensure the close() mehtod(s) get called.
As JDBC force us to do so (even if you do not use my wrapper) there should be no problem at all.

If you find any problem, just drop me a note - if i am not on vacation i might have a look.