Using Clobs/Blobs with Oracle and Hibernate

Hibernate 1.2.1 comes with support for Clobs (and Blobs). Just use the clob type in your mapping file and java.sql.Clob in your persistent class.

However, due to problems with the Oracle JDBC driver, this support falls short when you try to store more than 4000 characters in a Clob. In order to properly store Clobs in Oracle 8 with Hibernate 1.2.x, you must do the following:

s = sf.openSession();tx = s.beginTransaction();foo = new Foo();foo.setClob( Hibernate.createClob(" ") );s.save(foo);tx.commit();s.close();s = sf.openSession();tx = s.beginTransaction();foo = (Foo) s.load( Foo.class, foo.getId(), LockMode.UPGRADE );oracle.sql.CLOB clob = (oracle.sql.CLOB) foo.getClob();java.io.Writer pw = clob.getCharacterOutputStream();pw.write(content);pw.close();tx.commit();s.close();

You should be careful not to pass a zero-length string to Hibernate.createClob(), otherwise Oracle will set the column value to NULL and the subsequent getClob() call will return null.

In Hibernate2, the following (much more elegant) solution exists:

s = sf.openSession();tx = s.beginTransaction();foo = new Foo();foo.setClob( Hibernate.createClob(" ") );s.save(foo);s.flush();s.refresh(foo, LockMode.UPGRADE); //grabs an Oracle CLOBoracle.sql.CLOB clob = (oracle.sql.CLOB) foo.getClob();java.io.Writer pw = clob.getCharacterOutputStream();pw.write(content);pw.close();tx.commit();s.close();

If you need a solution that is more transparent and you can rely on having the Oracle 9.x JDBC drivers then you can try using the newly introduced oracle.sql.CLOB.createTemporary method. Here is an example user type that uses this idea while converting Clobs to strings. Note that it uses reflection to avoid a compile-time dependency on the Oracle driver, however the methods can be used directly if you wish. Also it should be straightforward to convert this UserType to one that just maps to a clob in the data object.

package foobar;import java.io.Reader;import java.io.BufferedReader;import java.io.StringReader;import java.io.IOException;import java.io.Writer;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;import java.sql.Clob;import java.sql.Connection;import java.sql.DatabaseMetaData;import net.sf.hibernate.Hibernate;import net.sf.hibernate.HibernateException;import net.sf.hibernate.UserType;import org.apache.commons.lang.ObjectUtils;/** * Based on community area design patterns on Hibernate site. * Maps java.sql.Clob to a String special casing for Oracle drivers. * @author Ali Ibrahim, Scott Miller */public class StringClobType implements UserType{  /** Name of the oracle driver -- used to support Oracle clobs as a special case */  private static final String ORACLE_DRIVER_NAME = "Oracle JDBC driver";    /** Version of the oracle driver being supported with clob. */  private static final int ORACLE_DRIVER_MAJOR_VERSION = 9;  private static final int ORACLE_DRIVER_MINOR_VERSION = 0;   public int[] sqlTypes()  {    return new int[] { Types.CLOB };  }  public Class returnedClass()  {    return String.class;  }  public boolean equals(Object x, Object y)  {    return ObjectUtils.equals(x, y);  }  public Object nullSafeGet(ResultSet rs, String[] names, Object owner)    throws HibernateException, SQLException  {    Reader clobReader = rs.getCharacterStream(names[0]);    if (clobReader == null)    {      return null;    }    String str = new String();    BufferedReader bufferedClobReader = new BufferedReader(clobReader);    try    {      String line = null;      while( (line = bufferedClobReader.readLine()) != null )      {    str += line;      }      bufferedClobReader.close();    }    catch (IOException e)    {      throw new SQLException( e.toString() );    }    return str;  }  public void nullSafeSet(PreparedStatement st, Object value, int index)    throws HibernateException, SQLException  {    DatabaseMetaData dbMetaData = st.getConnection().getMetaData();    if (value==null)    {      st.setNull(index, sqlTypes()[0]);    }    else if (ORACLE_DRIVER_NAME.equals( dbMetaData.getDriverName() ))    {      if ((dbMetaData.getDriverMajorVersion() >= ORACLE_DRIVER_MAJOR_VERSION) &&      (dbMetaData.getDriverMinorVersion() >= ORACLE_DRIVER_MINOR_VERSION))      {    try    {      // Code compliments of Scott Miller      // support oracle clobs without requiring oracle libraries      // at compile time      // Note this assumes that if you are using the Oracle Driver.      // then you have access to the oracle.sql.CLOB class                              // First get the oracle clob class      Class oracleClobClass = Class.forName("oracle.sql.CLOB");      // Get the oracle connection class for checking      Class oracleConnectionClass = Class.forName("oracle.jdbc.OracleConnection");                              // now get the static factory method      Class partypes[] = new Class[3];      partypes[0] = Connection.class;      partypes[1] = Boolean.TYPE;      partypes[2] = Integer.TYPE;                      Method createTemporaryMethod = oracleClobClass.getDeclaredMethod( "createTemporary", partypes );                              // now get ready to call the factory method      Field durationSessionField = oracleClobClass.getField( "DURATION_SESSION" );      Object arglist[] = new Object[3];      Connection conn = st.getConnection();      // Make sure connection object is right type      if (!oracleConnectionClass.isAssignableFrom(conn.getClass()))      {        throw new HibernateException("JDBC connection object must be a oracle.jdbc.OracleConnection. " +                                     "Connection class is " + conn.getClass().getName());      }      arglist[0] = conn;      arglist[1] = Boolean.TRUE;      arglist[2] = durationSessionField.get(null); //null is valid because of static field                              // Create our CLOB      Object tempClob = createTemporaryMethod.invoke( null, arglist ); //null is valid because of static method                              // get the open method      partypes = new Class[1];      partypes[0] = Integer.TYPE;      Method openMethod = oracleClobClass.getDeclaredMethod( "open", partypes );                                                      // prepare to call the method      Field modeReadWriteField = oracleClobClass.getField( "MODE_READWRITE" );      arglist = new Object[1];      arglist[0] = modeReadWriteField.get(null); //null is valid because of static field                              // call open(CLOB.MODE_READWRITE);      openMethod.invoke( tempClob, arglist );                              // get the getCharacterOutputStream method      Method getCharacterOutputStreamMethod = oracleClobClass.getDeclaredMethod( "getCharacterOutputStream", null );                              // call the getCharacterOutpitStream method      Writer tempClobWriter = (Writer) getCharacterOutputStreamMethod.invoke( tempClob, null );                              // write the string to the clob      tempClobWriter.write((String)value);       tempClobWriter.flush();      tempClobWriter.close();                               // get the close method      Method closeMethod = oracleClobClass.getDeclaredMethod( "close", null );                              // call the close method      closeMethod.invoke( tempClob, null );                              // add the clob to the statement      st.setClob( index, (Clob)tempClob );    }    catch( ClassNotFoundException e )    {      // could not find the class with reflection      throw new HibernateException("Unable to find a required class.\n" + e.getMessage());    }    catch( NoSuchMethodException e )    {      // could not find the metho with reflection      throw new HibernateException("Unable to find a required method.\n" + e.getMessage());    }    catch( NoSuchFieldException e )    {      // could not find the field with reflection      throw new HibernateException("Unable to find a required field.\n" + e.getMessage());    }    catch( IllegalAccessException e )    {      throw new HibernateException("Unable to access a required method or field.\n" + e.getMessage());    }    catch( InvocationTargetException e )    {      throw new HibernateException(e.getMessage());    }    catch( IOException e )    {      throw new HibernateException(e.getMessage());     }      }      else      {    throw new HibernateException("No CLOBS support. Use driver version " + ORACLE_DRIVER_MAJOR_VERSION +                                 ", minor " + ORACLE_DRIVER_MINOR_VERSION);      }    }        else    {      String str = (String)value;      StringReader r = new StringReader(str);      st.setCharacterStream(index, r, str.length());    }        }  public Object deepCopy(Object value)  {    if (value == null) return null;    return new String((String) value);  }    public boolean isMutable()  {    return false;  }}

Notes:

1. This approach is very fragile when not used directly with oracle jdbc connections. Somwhere in the createTemporary method the connection is cast to an oracle.jdbc.OracleConnection. Of course this means that the connection you give it must be assignable to that class. The code here checks for that and tries to throw a meaningful exception. The practical implication is that connection pooling mechanisms such as in web application servers or jdbc wrappers such as p6spy can break the code. The workaround is to somehow extract the underlying connection to give to the createTemporary method (this is usually straightforward as I have done this for p6spy and oc4j in my custom code).

2. Related to the first point, even though OC4J/Orion data source pooling class for Oracle actually is assignable to oracle.jdbc.OracleConnection, there were NullPointerExceptions being thrown. When I extracted the underlying connection through the getPhysicalConnection method, it worked, so I assume there is some wierdness with the behavior of the wrapper class (OrclCMTConnection).

Enjoy!

---

Updated Clobs handling for Oracle and Hibernate

- uses interceptor and avoids compile-time dependency.

[Lukasz's compilation (added on 14th Oct 2004)]

Just copy/paste it.

User type:

import net.sf.hibernate.HibernateException;import net.sf.hibernate.UserType;import org.apache.commons.lang.ObjectUtils;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import java.io.IOException;import java.io.StringReader;import java.io.Writer;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.sql.Clob;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;/** * Implementation of Oracle's CLOB handling */public class StringClobType implements UserType {    protected static Log log = LogFactory.getLog(StringClobType.class);    /** Name of the oracle driver -- used to support Oracle clobs as a special case */    private static final String ORACLE_DRIVER_NAME = "Oracle JDBC driver";    /** Version of the oracle driver being supported with clob. */    private static final int ORACLE_DRIVER_MAJOR_VERSION = 9;    private static final int ORACLE_DRIVER_MINOR_VERSION = 0;    /**     * @see net.sf.hibernate.UserType#nullSafeGet(java.sql.ResultSet, java.lang.String[], java.lang.Object)     */    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)        throws SQLException {        //Get the clob field we are interested in from the result set         Clob clob = rs.getClob(names[0]);        return ((clob == null) ? null : clob.getSubString(1, (int) clob.length()));    }    /**     * oracleClasses independent (at compile time); based on http://forum.hibernate.org/viewtopic.php?p=2173150,     * changes: changed line:  Connection conn = ps.getConnection(); to: Connection conn = dbMetaData.getConnection();     *     * @see net.sf.hibernate.UserType#nullSafeSet(java.sql.PreparedStatement, java.lang.Object, int)     */    public void nullSafeSet(PreparedStatement ps, Object value, int index)        throws SQLException, HibernateException {        DatabaseMetaData dbMetaData = ps.getConnection().getMetaData();        log.debug(dbMetaData.getDriverName());        log.debug(dbMetaData.getDriverMajorVersion() + " " + dbMetaData.getDriverMinorVersion());        log.debug(dbMetaData.getConnection().getClass().getName());        if (value == null) {            ps.setNull(index, sqlTypes()[0]);        } else if (ORACLE_DRIVER_NAME.equals(dbMetaData.getDriverName())) {            if ((dbMetaData.getDriverMajorVersion() >= ORACLE_DRIVER_MAJOR_VERSION) &&                    (dbMetaData.getDriverMinorVersion() >= ORACLE_DRIVER_MINOR_VERSION)) {                try {                    // Code compliments of Scott Miller                     // support oracle clobs without requiring oracle libraries                     // at compile time                     // Note this assumes that if you are using the Oracle Driver.                     // then you have access to the oracle.sql.CLOB class                     // First get the oracle clob class                     Class oracleClobClass = Class.forName("oracle.sql.CLOB");                    // Get the oracle connection class for checking                     Class oracleConnectionClass = Class.forName("oracle.jdbc.OracleConnection");                    // now get the static factory method                     Class[] partypes = new Class[3];                    partypes[0] = Connection.class;                    partypes[1] = Boolean.TYPE;                    partypes[2] = Integer.TYPE;                    Method createTemporaryMethod = oracleClobClass.getDeclaredMethod("createTemporary", partypes);                    // now get ready to call the factory method                     Field durationSessionField = oracleClobClass.getField("DURATION_SESSION");                    Object[] arglist = new Object[3];                    //changed from: Connection conn = ps.getConnection();                    Connection conn = dbMetaData.getConnection();                    // Make sure connection object is right type                     if (!oracleConnectionClass.isAssignableFrom(conn.getClass())) {                        throw new HibernateException("JDBC connection object must be a oracle.jdbc.OracleConnection. " +                            "Connection class is " + conn.getClass().getName());                    }                    arglist[0] = conn;                    arglist[1] = Boolean.TRUE;                    arglist[2] = durationSessionField.get(null); //null is valid because of static field                     // Create our CLOB                     Object tempClob = createTemporaryMethod.invoke(null, arglist); //null is valid because of static method                     // get the open method                     partypes = new Class[1];                    partypes[0] = Integer.TYPE;                    Method openMethod = oracleClobClass.getDeclaredMethod("open", partypes);                    // prepare to call the method                     Field modeReadWriteField = oracleClobClass.getField("MODE_READWRITE");                    arglist = new Object[1];                    arglist[0] = modeReadWriteField.get(null); //null is valid because of static field                     // call open(CLOB.MODE_READWRITE);                     openMethod.invoke(tempClob, arglist);                    // get the getCharacterOutputStream method                     Method getCharacterOutputStreamMethod = oracleClobClass.getDeclaredMethod("getCharacterOutputStream",                            null);                    // call the getCharacterOutpitStream method                     Writer tempClobWriter = (Writer) getCharacterOutputStreamMethod.invoke(tempClob, null);                    // write the string to the clob                     tempClobWriter.write((String) value);                    tempClobWriter.flush();                    tempClobWriter.close();                    // get the close method                     Method closeMethod = oracleClobClass.getDeclaredMethod("close", null);                    // call the close method                     closeMethod.invoke(tempClob, null);                    // add the clob to the statement                     ps.setClob(index, (Clob) tempClob);                    LobCleanUpInterceptor.registerTempLobs(tempClob);                } catch (ClassNotFoundException e) {                    // could not find the class with reflection                     throw new HibernateException("Unable to find a required class.\n" + e.getMessage());                } catch (NoSuchMethodException e) {                    // could not find the metho with reflection                     throw new HibernateException("Unable to find a required method.\n" + e.getMessage());                } catch (NoSuchFieldException e) {                    // could not find the field with reflection                     throw new HibernateException("Unable to find a required field.\n" + e.getMessage());                } catch (IllegalAccessException e) {                    throw new HibernateException("Unable to access a required method or field.\n" + e.getMessage());                } catch (InvocationTargetException e) {                    throw new HibernateException(e.getMessage());                } catch (IOException e) {                    throw new HibernateException(e.getMessage());                }            } else {                throw new HibernateException("No CLOBS support. Use driver version " + ORACLE_DRIVER_MAJOR_VERSION +                    ", minor " + ORACLE_DRIVER_MINOR_VERSION);            }        } else {            String str = (String) value;            StringReader r = new StringReader(str);            ps.setCharacterStream(index, r, str.length());        }    }    public Object deepCopy(Object value) {        if (value == null) {            return null;        }        return new String((String) value);    }    public boolean isMutable() {        return false;    }    public int[] sqlTypes() {        return new int[] { Types.CLOB };    }    public Class returnedClass() {        return String.class;    }    public boolean equals(Object x, Object y) {        return ObjectUtils.equals(x, y);    }}

Interceptor:

import net.sf.hibernate.Interceptor;import net.sf.hibernate.type.Type;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import java.io.Serializable;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.util.HashSet;import java.util.Iterator;import java.util.Set;public class LobCleanUpInterceptor implements Interceptor {    protected static Log log = LogFactory.getLog(LOBEntityInterceptor.class);    // a thread local set to store temperary LOBs    private static final ThreadLocal threadTempLobs = new ThreadLocal();    public boolean onLoad(Object arg0, Serializable arg1, Object[] arg2, String[] arg3, Type[] arg4) {        return false;    }    public boolean onFlushDirty(Object arg0, Serializable arg1, Object[] arg2, Object[] arg3, String[] arg4, Type[] arg5) {        return false;    }    public boolean onSave(Object arg0, Serializable arg1, Object[] arg2, String[] arg3, Type[] arg4) {        return false;    }    public void onDelete(Object arg0, Serializable arg1, Object[] arg2, String[] arg3, Type[] arg4) {}    public void preFlush(Iterator arg0) {}    public Boolean isUnsaved(Object arg0) {        return null;    }    public int[] findDirty(Object arg0, Serializable arg1, Object[] arg2, Object[] arg3, String[] arg4, Type[] arg5) {        return null;    }    public Object instantiate(Class arg0, Serializable arg1) {        return null;    }    public void postFlush(Iterator arg0) {        Set tempLobs = (Set) threadTempLobs.get();        if (tempLobs == null) {            return;        }        try {            for (Iterator iter = tempLobs.iterator(); iter.hasNext();) {                Object lob = iter.next();                Method freeTemporary = lob.getClass().getMethod("freeTemporary", new Class[0]);                freeTemporary.invoke(lob, new Object[0]);                log.info("lob cleaned");            }        } catch (SecurityException e) {            log.error("clean LOB failed: " + e.getMessage(), e);            throw new RuntimeException(e);        } catch (NoSuchMethodException e) {            log.error("clean LOB failed: " + e.getMessage(), e);            throw new RuntimeException(e);        } catch (IllegalArgumentException e) {            log.error("clean LOB failed: " + e.getMessage(), e);            throw new RuntimeException(e);        } catch (IllegalAccessException e) {            log.error("clean LOB failed: " + e.getMessage(), e);            throw new RuntimeException(e);        } catch (InvocationTargetException e) {            log.error("clean LOB failed: " + e.getMessage(), e);            throw new RuntimeException(e);        } finally {            threadTempLobs.set(null);            tempLobs.clear();        }    }    // register oracle temperary BLOB/CLOB into     // a thread-local set, this should be called at    // the end of nullSafeSet(...) in BinaryBlobType    // or StringClobType    public static void registerTempLobs(Object lob) {        getTempLobs().add(lob);    }    // lazy create temperary lob storage    public static Set getTempLobs() {        Set tempLobs = (Set) threadTempLobs.get();        if (tempLobs == null) {            tempLobs = new HashSet();            threadTempLobs.set(tempLobs);        }        return tempLobs;    }}

things that you need to do (beside copy/paste):

1. if using Oracle - use Oracle's 9 (or grater) drivers

1. obtain session with interceptor

sessionFactory.openSession(new LobCleanUpInterceptor());

1. use it:

<property name="lobField" column="whatever" type="StringClobType"/>

Tested on Oracle 8i and 9i (Oracle 9 drivers; ojdbc14 9.2.0.5), HSQLDB 1.7.2, MySql 4.0 (Connector 3.0.15-ga).

Note for MySql users: - CLOB becomes TEXT and it can hold only up to 65k, if you need more add length="16777215" to your column mapping for MEDIUMTEXT or add more for LONGTEXT.

---

Another solution for Oracle 8i

- does not need an Interceptor, works with 8i JDBC drivers

By Eli Levine [elilevine _AT_ yahoo] October 18, 2004

This implementation requires two additional DB objects, a sequence and a temporary table, created as such:

-- create tableCREATE GLOBAL TEMPORARY TABLE TEMP_CLOB_TABLE (   ID         NUMBER,   TEMP_CLOB  CLOB) ON COMMIT DELETE ROWS; -- create sequenceCREATE SEQUENCE SEQ_TEMP_CLOB_ID INCREMENT BY 1 START WITH 0 MINVALUE 0 MAXVALUE 99999999 CYCLE NOCACHE NOORDER;

UserType implementation:

import java.io.IOException;import java.io.Writer;import java.sql.Clob;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;import net.sf.hibernate.HibernateException;import net.sf.hibernate.UserType;import oracle.sql.CLOB;public class StringClobTypeUsingTempTable implements UserType {    /**     * @return java.sql.Types.CLOB     */    public int[] sqlTypes() {        return new int[] { Types.CLOB };    }    /**     * @return java.lang.String.class     */    public Class returnedClass() {        return String.class;    }        public boolean equals(Object x, Object y) throws HibernateException {        return (x == y) || (x != null && y != null && (x.equals(y)));    }        /**     * @see net.sf.hibernate.UserType#nullSafeGet(java.sql.ResultSet, java.lang.String[], java.lang.Object)     */    public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {        Clob clob = rs.getClob(names[0]);        return clob.getSubString(1, (int) clob.length());    }    /**     * @see net.sf.hibernate.UserType#nullSafeSet(java.sql.PreparedStatement, java.lang.Object, int)     */    public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {                int tempClobId;                Connection con = st.getConnection();        Statement sta;        ResultSet rs;                String sql = "select seq_temp_clob_id.nextval from dual";                sta = con.createStatement();        rs = sta.executeQuery(sql);        rs.next();        tempClobId = rs.getInt(1);                rs.close();        sta.close();                sta = con.createStatement();        sql = "insert into temp_clob_table (id, temp_clob) values(" + tempClobId + ", empty_clob())";        sta.executeUpdate(sql);                sta.close();                sta = con.createStatement();        sql = "select temp_clob from temp_clob_table where id=" + tempClobId+ " for update";        sta = con.createStatement();        rs = sta.executeQuery(sql);                rs.next();                    CLOB tempClob = (CLOB)rs.getClob(1);                    Writer tempClobWriter = tempClob.getCharacterOutputStream();        try {            tempClobWriter.write((String)value);            tempClobWriter.flush();            tempClobWriter.close();        } catch (IOException ioe) {            throw new HibernateException(ioe);        }                rs.close();        sta.close();                st.setClob(index, tempClob);    }    /**     * @see net.sf.hibernate.UserType#deepCopy(java.lang.Object)     */    public Object deepCopy(Object value) throws HibernateException {        if (value == null) return null;        return new String((String) value);    }    /**     * @return false     * @see net.sf.hibernate.UserType#isMutable()     */    public boolean isMutable() {        return false;    }}

No Interceptor is needed to clean up the temporary CLOB because it is created in the global temporary table and is cleared by the database on commit.

---

Dealing with BLOBs (Interceptor method)

User custom type ByteArrayBlobType

-23/02/2005.[sebastien.fabbri#at#gmail]

Additional information concerning usage of BLOBs in the same manner for Weblogic 8.1 and Oracle 9.2 using Spring 1.1.3 and Hibernate 2.1.7.

note: see [ http://saloon.javaranch.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic&f=78&t=000548 ] which would be a really cool if I was able to make it working ;(

Based on the solution of the UserType + Interceptor already mentionned that works great, here is the implementation of the ByteArrayBlobType to map to attribute byte[] in the DAO Object:

import net.sf.hibernate.HibernateException;import net.sf.hibernate.UserType;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import java.io.IOException;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.sql.Blob;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;import net.sf.hibernate.HibernateException;import net.sf.hibernate.UserType;import oracle.sql.BLOB;import org.apache.commons.lang.ObjectUtils;import org.apache.log4j.Logger;import weblogic.jdbc.extensions.WLConnection;/** */public class ByteArrayBlobType implements UserType {    private Logger log = Logger.getLogger(getClass());    /**     * Return the SQL type codes for the columns mapped by this type.      */    public int[] sqlTypes() {        return new int[] { Types.BLOB};    }    /**     * The class returned by <tt>nullSafeGet()</tt>.     */    public Class returnedClass() {        return String.class;    }    public boolean equals(Object x, Object y) {        return ObjectUtils.equals(x, y);    }    /**     * Retrieve an instance of the mapped class from a JDBC resultset.Implementors     * should handle possibility of null values.     */    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)        throws HibernateException, SQLException {        InputStream blobReader = rs.getBinaryStream(names[0]);        if (blobReader == null)             return null;        byte[] b = new byte[1024];        ByteArrayOutputStream os = new ByteArrayOutputStream();        try {            while ((blobReader.read(b)) != -1)                 os.write(b);        } catch (IOException e) {            throw new SQLException(e.toString());        } finally {            try {                os.close();            } catch (IOException e) {            }        }        return os.toByteArray();    }    /**     * Write an instance of the mapped class to a prepared statement.Implementors     * should handle possibility of null values. A multi-column type shouldbe written     * to parameters starting from <tt>index</tt>.     *      */    public void nullSafeSet(PreparedStatement st, Object value, int index)            throws HibernateException, SQLException {                if (value == null) {            st.setNull(index, sqlTypes()[0]);            return;        }                try {            Connection conn =st.getConnection().getMetaData().getConnection();                        if (conn instanceof WLConnection)                conn = ((WLConnection)conn).getVendorConnection();            log.info(conn.getClass().getName());                        OutputStream tempBlobOutputStream = null;            BLOB tempBlob = BLOB.createTemporary(conn, true,BLOB.DURATION_SESSION);            try {                tempBlob.open(BLOB.MODE_READWRITE);                tempBlobOutputStream = tempBlob.getBinaryOutputStream();                tempBlobOutputStream.write((byte[])value);                tempBlobOutputStream.flush();            } finally {                if (tempBlobOutputStream != null)                    tempBlobOutputStream.close();                tempBlobOutputStream.close();            }            st.setBlob(index, (Blob) tempBlob);        } catch (IOException e) {            throw new HibernateException(e);        }    }    /**     * Return a deep copy of the persistent state, stopping at entities and at     * collections.     */    public Object deepCopy(Object value) {        return (byte[])value;    }    /**     * Are objects of this type mutable?     */    public boolean isMutable() {        return false;    }}

---

Another solution for Oracle 9i

- does not need any change to the mapping

Just use this jdbc-wrapper and any setString/getString will automatically deal with clob

http://l3x.net/imwiki/Wiki.jsp?page=JdbcOraWrapper

---

CLOBs larger than 4K characters in Oracle 8

If you use the Oracle OCI driver in stead of the JDBC Thin driver, you can use CLOBs upto 4GB if you want. You can download the drivers from Oracle.com. Do note that you need to install a native Oracle library (in other words, you need extended admin privileges on your server), but it works like a charm.

The URL for connecting using OCI is: jdbc:oracle:oci8:@ORCL where ORCL is the TNS name for your database on the servers TNS list.

Hope this adds some value ;)

---

Beware - lots of info on this page outdated!

October 04, 2006, mikewse

Most of the workarounds found on this page are for ancient Oracle JDBC driver versions and old Hibernate versions, so there is a big chance you do not need the "fixes" suggested here. The crucial bug fixes in the Oracle JDB driver have been in place at least since early 2005, when my current project threw away all the error-prone "fix" code from this page and started doing things the "standard" way. (Disclaimer: we have been on Oracle 9.x for some time so I cannot speak for Oracle 8.x environments.)

My recommendations:

Get the latest JDBC driver

1. download from Oracle
2. use the thin driver (don't need OCI)
3. the latest Oracle 10.x driver has all the latest fixes and works with 9.x databases
4. also update the driver used/bundled by your appserver if applicable

CLOBS

1. if all you want is to load the CLOB contents into a text field then map it with Hibernate type "text" and all CLOB handling will be done transparently for you

LOB locators and transactions

1. note that Oracle LOB locators are normally bound to the current transaction, so if you load a Hibernate bean with a LOB member in a long session you will not be able to access the LOB content in subsequent transactions (Oracle will complain about closed connection which is only half true)