"ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column".
I searched for this exception on Google and found that many people have faced this error. Then, I thought of writing an article so that I share my knowledge with others.
Root Cause of ErrorFrom some reading around me, I found that it turns out having a mixture of CLOB and varchar2(4000) fields in a database causes some issues for Oracle, especially during an insert. Oracle requires all LOB columns to be the last in the statement. If all LOB columns are not the last in the statement then it throws this error.
This error occurs when there is more than 4000 bytes of data following a LOB bind variable. This means that you might run into this problem as soon as you have a LOB and say a varchar2(4000) in the same table.
Generally, we use JPA/Hibernate in our applications to get relieved from writing pure SQL queries. JPA/Hibernate generate SQL queries to perform any operation (like insert, update or delete etc.) on database and we can't control the structure of query generated by JPA/Hibernate.
SolutionBelow is the list of possible solutions to fix this error:
- Alter all varchar2(4000) to be CLOB fields.
- If you are using JPA/Hibernate then renaming the LOB fields in the hibernate model so that the LOB column has a name that comes later than the varchar2 column when ordering alphabetically then everything works fine because the LOB parameter comes after the varchar parameter in the query hibernate builds.
- Put the LOB in a new table with a one to one mapping from parent to child table. This way the only data that may get inserted after the lob bind variable would be the primary key value.
- It can solved by writing two update queries. Firstly, save/update the entity by an Update query and then write another query to update LOB columns in that entity.
- Rearranging the Getter and Setter methods in Hibernate. Unfortunately, there is one case where that doesn't work: If you use joined inheritance, Hibernate will put the primary key join column last. And there is no way to change that, other than patching Hibernate.