There has been a bit of an on-going saga with this error message appearing from time to time in a live production environment. Today was the day that a real investigation was required to get to the root of the issue.
From some reading around it turns out having a mixture of CLOBs and varchar2(4000) fields in a database causes some issues for Oracle, especially during an insert. To overcome this the CLOB(s) must be the last parameters inserted in the SQL statement.
Unfortunately we use TopLink/JPA and so aren’t in control of our query structure.
Alter all varchar2(4000) to be CLOBs. In order to do this you’d need to run the following queries:
alter table <table> add <field>_copy varchar2(4000) update <table> set <field>_copy = <field> update <table> set <field> = null commit</div> alter table <table> modify <field> long alter table <table> modify <field> clob update <table> set <field> = <field>_copy alter table <table> drop column <field>_copy commit
After doing this I encountered an issue with the index link to this table not being correct. To resolve this issue:
alter index <index> rebuild
Run the query as normal and omit the setting of the CLOB(s) field.
After persisting the original query, lookup the bean and update the CLOBs fields before persisting again.