Parameters order is not predictable leading to ORA-24816 when inserting a row into a table with Unlimited string column and another (limited) string column

Description

Oracle requires that LOB / LONG parameters should come last in the INSERT / UPDATE statements, or at least not before any VARCHAR parameters.

Unfortunately, AO ignores this fact and EntityManager.create() randomly orders the inserted parameters because they are collected via a HashSet. Which may lead to BLOB column parameter coming before a VARCHAR column parameter, which then produces this error:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Caused by: java.sql.SQLException: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3897) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98) at net.java.ao.db.OracleDatabaseProvider.executeInsertReturningKey(OracleDatabaseProvider.java:294) at net.java.ao.DatabaseProvider.insertReturningKey(DatabaseProvider.java:1861) at net.java.ao.EntityManager.create(EntityManager.java:366) at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:92) ... 267 more

Environment

Oracle DB

Testing Notes

Add notes...

Status

Assignee

Unassigned

Reporter

Igor Sereda [ALM Works]

Labels

None

Add-on Type

None

Team

None

CC

None

Risk factor

None

QA Kickoff Status

None

QA Demo Status

None

Priority

Major
Configure