ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

There has been a bit of an on-going saga with this error message appearing from time to time and most of the time, it is in a live production environment. This error comes when we have Oracle as database. I got this error message today while I was saving data and I got an exception. Basically, we maintain an error log in database to track error logs. Recently, we changed our error log schema and when my application was trying to save data in error log then it threw an exception and showed this error message in logs.

"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 Error

From 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.

Solution

Below 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.

I tried to explain with my best. If you think that there is any mistake then please feel free to post comment so that I could correct that mistake and could change the content accordingly.