Support Article
BIX Insert query displays ORA-24816 when multiple clob columns
SA-78894
Summary
In a Business Intelligence Exchange (BIX) Extract rule, multiple properties (in the Extract rule) are mapped as Long text to a CLOB column in an Oracle database. These Long text properties, on different pages, are all extracted to the same target top level table. ORA-24816 occurs when the work objects are extracted.
Error Messages
Severe SQL Exception: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column
java.sql.BatchUpdateException: ORA-24816: Expanded non LONG bind data supplied after actual LONG or CLOB column
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:12296) ~[ojdbc7.jar:12.1.0.2.0]
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:246) ~[ojdbc7.jar:12.1.0.2.0]
Steps to Reproduce
- Create an Extract rule with any Work class and set the type as a Database Extract.
- Create a main database table for the main class.
- Add a page.
- Add sub pages to the main page as part of the Work class.
- In one of the sub pages,
- Add a pyNote at the end of the sub page
- Set the column type as Long text
- Set the length as 8000 characters
- After adding pyNote to the end of the last sub page, add another parent page to the main class.
- Include additional fields to the parent page.
- Create the table in the database.
- While creating the table in the database, define pyNote datatype as a CLOB and execute the query.
- Return to the Extract and run it manually. Error is generated in the logs after the Extract is run because the Extract is unable to add the CLOB columns at the end of the Insert query.
Root Cause
A defect or configuration issue in the operating environment.
Under certain circumstances, Oracle requires all the Bind variables, in an Insert statement for CLOB columns, to be appended at the end of the list.
Resolution
Apply HFix-52259.
Published May 2, 2019 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.