As part of upgrading JRun, we are moving from a 1.4 JVM to a 1.6 JVM. Now I am getting a really strange oracle db error: "OALL8 is in an inconsistent state". I have pinned down the problem to insert queries that do not use bind variables at all - all inline parameters. If I run the query without any bind variables, I get the above error. As soon as I replace one of the hard coded values with a bind variable - everything works without error.
The other strange bit is that after executing the query, it is in fact committed to the database. I can connect from another session and see the inserted row. I have tried wrapping the query in a transaction and it seems to succeed as the behavior is unchanged from the query without an explicit transaction.
Here are the relevant details:
Java Version: 1.6.0_12-b04
Virtual Machine Version: 11.2-b01 (HotSpot Server)
Oracle Server: 10.2.0.4
Oracle Client: 11.1.0.7.0 through ojdbc6.jar
Update: I am using cfqueryparam - they are called bind variables in the oracle world. While that does solve the immediate problem, we have a rather large legacy code base that we can't realistically go through all of to update the queries as part of upgrading from CF7 to CF8.
Even though I have pinned down one specific situation that fails (and encapsulated it in an mxunit test) - that doesn't mean there aren't other areas where this may be an issue. I would really like to have a solution in place that removes the OALL8 error rather than coding around it.
Update 2: After checking with our DBA he had set a parameter called CURSOR_SHARING to SIMILAR. The Oracle default is EXACT. What is happening is when ColdFusion hands the query off to be executed, Oracle is turning all the literal values to bind variables and that appears to be confusing ColdFusion. Turning the setting back to EXACT allows the literal queries to work just fine.
Update 3: Oracle finally issued us an out-of-band patch for JDBC. It was identified as a JDBC error. The latest drivers should include it when they are finally updated. If you have support you can also request the patch through their TAR system.