I am using Oracle Database 11g Release 11.2.0.4.0 - 64bit database and WildFly 8.2.0 Application Server. I am using managed connection in our application and XA transactions.
When I try to execute a query that is very complex (number of parameters of select > 1400 - number of used alias > 1400 - number of joins > 1400), these exception are returned:
2016-01-05 10:20:48,108 WARN [org.jboss.jca.core.connectionmanager.listener.TxConnectionListener] (default task-1) IJ000305: Connection error occured: org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@f1bbadb[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@6e35335 connection handles=1 lastUse=1451985632822 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@3f76ea19 mcp=SemaphoreArrayListManagedConnectionPool@529c5c92[pool=datamodel-oracleDS] xaResource=XAResourceWrapperImpl@18032fdc[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@6e35335 pad=false overrideRmValue=false productName=Oracle productVersion=Oracle Database 11g Release 11.2.0.4.0 - 64bit Production jndiName=java:/datamodel-oracleDS] txSync=null]: java.lang.ArrayIndexOutOfBoundsException: 128
...
2016-01-05 10:20:48,217 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-1) SQL Error: 0, SQLState: null
2016-01-05 10:20:48,219 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-1) Error
2016-01-05 10:20:48,260 ERROR [org.jboss.as.ejb3] (default task-1) javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.GenericJDBCException: could not execute query
...
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
...
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2557) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2540) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.loader.Loader.list(Loader.java:2365) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573) [hibernate-entitymanager-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:495) [hibernate-entitymanager-4.3.7.Final.jar:4.3.7.Final]
... 389 more
Caused by: java.sql.SQLException: Error
at org.jboss.jca.adapters.jdbc.WrappedConnection.checkException(WrappedConnection.java:1648)
at org.jboss.jca.adapters.jdbc.WrappedStatement.checkException(WrappedStatement.java:1267)
at org.jboss.jca.adapters.jdbc.WrappedResultSet.checkException(WrappedResultSet.java:4063)
at org.jboss.jca.adapters.jdbc.WrappedResultSet.next(WrappedResultSet.java:1866)
at org.hibernate.loader.Loader.processResultSet(Loader.java:950) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.loader.Loader.doQuery(Loader.java:921) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2554) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
... 399 more
Caused by: java.lang.ArrayIndexOutOfBoundsException: 128
2016-01-05 10:20:48,529 WARN [com.arjuna.ats.jta] (default task-1) ARJUNA016045: attempted rollback of < formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffff7f000001:1d0483f3:568b8971:1c, node_name=1, branch_uid=0:ffff7f000001:1d0483f3:568b8971:2c, subordinatenodename=null, eis_name=java:/datamodel-oracleDS > (XAResourceWrapperImpl@18032fdc[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@6e35335 pad=false overrideRmValue=false productName=Oracle productVersion=Oracle Database 11g Release 11.2.0.4.0 - 64bit Production jndiName=java:/datamodel-oracleDS]) failed with exception code XAException.XAER_RMFAIL: oracle.jdbc.xa.OracleXAException
...
I am trying to fix it, I have searched many links, websites, blogs but without results. I have tried everything but I'm not able to fix the problem.
It is something that is causing the problem from the XA Transaction but I'm not able to identify the exact reason. I just noticed that reducing the number of parameters and joins of the query, the problem no longer occurs, so I think that it is related to a fixed limit on these numbers in the DB.
Please help me out to find out the exact cause of this exception and the solution as well. Thank you in advance.
