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

Solution 1

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

Solution 2

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.

References

Ask Tom – Change a Column varchar2 to CLOB
Indexes Left in an Unstable State

Advertisements

One thought on “ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s