Odi's astoundingly incomplete notes
New entries | CodeORA-24816 and Hibernate
This is a funny story. We experienced a strange Oracle error last night:
I had never met that beast before. Ignore the fact that the error message is completely cryptic. Ignore the fact that Oracle would never mention the name of the table and column that causes the problem. The table in question was easy enough to find. It contains about 70 columns (a typical number for business data from a legacy system). Three of them had recently been converted to CLOBs, as they could exceed 4000 characters.
At a first glance the error looks completely random and really looks like a driver issue. But the case was at least reproducible with some well-known data. So I considered an Oracle JDBC driver update to 10.0.2.0.4. Looking at the long changelog that promises fixes for some scary bugs I hoped that they had fixed this one as well. But no, the error still occurs with this latest version.
As some postings suggested, rearranging the column order (can you believe it!!!) might help. Well, we are not controlling the SQL manually, but EJB3 with Hibernate as an implementation does that in the backend. Turning on SQL logging revealed that Hibernate chose to arrange the columns so that two LOB columns were next to each other. Interestingly the two columns were also adjacent to each other in the entity bean definition (mappping). In a desperate act I just rearranged the getters and setters for this field, so that there were plenty of non-LOB columns in between the two LOBs. It seems to work.
UPDATE: Actually it turns out that Oracle says it's a limitation, and that LONG bind variables must come last in a statement. That's easily done by rearranging the getters and setters 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.
If you can learn anything from that, then it is how crucial of a feature it is for an O/R mapping framework to give the developer complete control over the generated SQL if he needs to. It's the number one mistake of O/R frameworks to not allow that. This poses a very hard wall that you will sooner or later hit in any project.
ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB columnI had never met that beast before. Ignore the fact that the error message is completely cryptic. Ignore the fact that Oracle would never mention the name of the table and column that causes the problem. The table in question was easy enough to find. It contains about 70 columns (a typical number for business data from a legacy system). Three of them had recently been converted to CLOBs, as they could exceed 4000 characters.
At a first glance the error looks completely random and really looks like a driver issue. But the case was at least reproducible with some well-known data. So I considered an Oracle JDBC driver update to 10.0.2.0.4. Looking at the long changelog that promises fixes for some scary bugs I hoped that they had fixed this one as well. But no, the error still occurs with this latest version.
As some postings suggested, rearranging the column order (can you believe it!!!) might help. Well, we are not controlling the SQL manually, but EJB3 with Hibernate as an implementation does that in the backend. Turning on SQL logging revealed that Hibernate chose to arrange the columns so that two LOB columns were next to each other. Interestingly the two columns were also adjacent to each other in the entity bean definition (mappping). In a desperate act I just rearranged the getters and setters for this field, so that there were plenty of non-LOB columns in between the two LOBs. It seems to work.
UPDATE: Actually it turns out that Oracle says it's a limitation, and that LONG bind variables must come last in a statement. That's easily done by rearranging the getters and setters 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.
If you can learn anything from that, then it is how crucial of a feature it is for an O/R mapping framework to give the developer complete control over the generated SQL if he needs to. It's the number one mistake of O/R frameworks to not allow that. This poses a very hard wall that you will sooner or later hit in any project.
We find out that Hibernate sorts the list in insert alfabetically by names of the fields in the class.
We just added an 'x' before each lob field and it worked
Insert was made to a table with some clob fields and one varchar(1500). The problem seemed to occur when both the value saved to clob and the one saved to varchar were longer than 1000 symbols.
Rearranging column order was too hard to do, instead I changed the varchar(1500) to clob as well.
It seems to work for now at least.