Weblogic12c: SQLException Invalid column index

Intermittently occurring problems are always hard to fix. I encountered one today in a webapp running on Weblogic12c connecting to Oracle database. There were random incidences of following exception in using a PreparedStatement:

  java.sql.SQLException: Invalid column index

    at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5330)

    at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5318)

    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:282)

    at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:903)

The same code would work just fine in normal operations and had no clue as to why above would occur every now and then, even with same set of data. I have overly simplified it but the code line from which this sporadic exceptions was thrown seemed something like this:

   pstmt.setString(1,somevalue);

Typically, one may think of binding("?") mismatch in SQL and setting them from java-code, but that could be ruled out as the app has been working just fine, barring rare occassions of above exception. Moreover, the SQL used to construct the PreparedStatement is defined in a static-final-variable, and no question of changing it dynamically. So what could cause this SQL Exception for some http-requests and not for the other?

In doing some extensive load-testing, we observed an increased frequency of above exceptions. And then in looking for patterns, it turned out if two shortly-spaced http-requests exercised the same PreparedStatement, above exception may occur. Try increasing the delay between those two requests and there won't be a problem. Or do something else in-between to exercise other code, other PreparedStatements/Statements etc, and you won't reproduce it.

With that in mind, I created a simple HtmlUnit based traversing of the app-pages to quickly exercise the same PreparedStatement, and managed to reproduce the intermittent problem more frequently. I deliberately chose to use the same set of data for requests and snooped the http-traffic. The requests appeared the same on the wire, and with same set of key-values, the first request used to work and the subsequent one in shorter time-span would fail.

The SQL wasn't changing, the data wasn't changing, therefore, I ruled out code-related errors and turned my attention to Weblogic DataSource configuration. After fiddling with some related Connection-Pool parameters, I stepped on Statement-Cache-Sizing parameter, and here is the description from Weblogic12c Admin Console:

Statement Cache Sizing:

The number of prepared and callable statements stored in the cache. (This may increase server performance.) WebLogic Server can reuse statements in the cache without reloading the statements, which can increase server performance. Each connection in the connection pool has its own cache of statements. Setting the size of the statement cache to 0 turns off statement caching.

In optimizing things, I guess Weblogic12c may have a bug with Statement-Cache-Sizing that surfaces in certain situations, and disabling this caching took care of the "SQLException: Invalid column index" problem for me. It is not ideal, but nevertheless a workaround.

Note the performance implications, before using it in production-environment.

How to disable Statement-Cache-Sizing?

Admin-Console-->Services--->Data Sources-->(select-your-DS)-->Connection Pool

and set Statement-Cache-Size = 0

Post alternatives if you come across any, otherwise, happy-surfing!