Strange Oracle error with Hibernate and CLOBs

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 a开发者_运维问答fter 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?

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.

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