Strange Oracle error with Hibernate and CLOBs

Question

I've run into the following issue, which seems to be a pretty common one. The exception is Cannot update entity: [...] nested exception is java.sql.BatchUpdateException: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column. It looks like Oracle does not like binding large values (> 4000 chars) to parameters after a LOB or CLOB. Has anyone solved this issue?

Answer 1

This is: ORA-24816
**It's a limitation, and that LONG bind variables must come last in a statement. **

source: http://www.odi.ch/weblog/posting.php?posting=496

Solution: By renaming the fields in the hibernate model so that the clob column has a name that comes later than the varchar2 column when ordering alphabetically (I prefixed the clob field in the java class with a 'z'), everything works fine because then the clob parameter comes after the varchar parameter in the query hibernate builds.

Answer 2

We encountered the same issue with Hibernate 3.2.1 and fixed by inserting the record without CLOBs first and then updating that record with CLOBs.

public class Employee{

    @Lob
    @Column
    private String description;

    //getters setters
}

String desc = emp.getDescription();
emp.setDescription(null);
session.save(entity);
session.flush();
session.evict(entity);

StringBuilder sb = new StringBuilder();
sb.append("update Employee set description:description");
Query updateQuery = session.createQuery(sb.toString());
updateQuery.setParameter("description", desc, Hibernate.STRING);
updateQuery.executeUpdate();

If you are using Hibernate annotations, then there is no way to predict the order the columns in insert statement. This was fixed in Hibernate v4.1.8.

https://hibernate.atlassian.net/browse/HHH-4635