Problem retrieving BLOBs from Oracle database

AndyOverton · **Posted:** Tue Mar 21, 2006 11:45 am

Hibernate version: 3.1

Oracle version: XE

I am trying to retrieve a BLOB from an Oracle database and have been struggling with this for days now. I have no problem with putting the BLOB in the database and can retrieve it fine using TOAD or basic SQL but when i try to retrieve it using Hibernate the object comes back fine but the byte array that should contain the BLOB comes back empty.

I have tried numerous approaches including :

Adding the following to my config file :

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

as suggested here - http://www.hibernate.org/56.html.

I have tried using a BinaryBlobType as suggested here :

http://www.hibernate.org/73.html

and ByteArrayBlobType as suggested here :

http://www.hibernate.org/56.html

I have tried multiple different drivers (Tried using ojdbc14.jar, classes12.jar, Oranxo.jar (inet softwares Oracle driver), the DataDirect drivers).

I've read through the various suggestions on the forum but most of the posts on there seem to cover putting the BLOB into the database which i have no problem with.

The code i am using to retrieve the data is :

Code:

Session session = null;

try
{
   session = longTermSessionFactory.getCurrentSession();

   // Try to retrieve the Information Form Response.
   Query query = session.createQuery("FROM InformationFormResponse " + "WHERE id='" + id + "'");

   InformationFormResponse response =               (InformationFormResponse) query.uniqueResult();

   return response;
}
catch (HibernateException he)

I am using Xdoclet for the mapping. The method is defined as:

Code:

* @hibernate.property type="blob"
*/
public byte[] getBytes()
{
   return attachmentContent;
}

Any help anyone can give greatly appreciated as i'm completely stumped.

anthony · **Posted:** Tue Mar 21, 2006 12:00 pm

have you tried ojdbc14.jar RELEASE 2 ?

AndyOverton · **Posted:** Tue Mar 21, 2006 12:22 pm

I am using the 10g release 2 (10.2.0.1.0) driver.

ponticello · **Posted:** Tue Mar 21, 2006 7:32 pm

I found this on a blog, it may help:

Make sure that you have hibernate.jdbc.use_streams_for_binary=true in hibernate.properties file (if you have the hibernate.jdbc.use_streams_for_binary in the hibernate.cfg.xml it will not be used). In the hibernate code, they are using hibernate.properies for 2 properties

http://hansonchar.blogspot.com/2005/06/oracle-blob-mapped-to-byte-in.html