Oracle:OALL8 处于不一致状态

问题描述

作为升级 JRun 的一部分，我们正在从 1.4 JVM 迁移到 1.6 JVM.现在我收到一个非常奇怪的 oracle db 错误:OALL8 处于不一致状态".我已经确定了插入根本不使用绑定变量的查询的问题 - 所有内联参数.如果我在没有任何绑定变量的情况下运行查询，则会收到上述错误.只要我用绑定变量替换其中一个硬编码值 - 一切都会正常工作.

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.

以下是相关细节:

Java 版本:1.6.0_12-b04
虚拟机版本:11.2-b01(HotSpot 服务器)
Oracle 服务器:10.2.0.4
Oracle 客户端:11.1.0.7.0 到 ojdbc6.jar

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

更新: 我正在使用 cfqueryparam - 它们在 oracle 世界中被称为绑定变量.虽然这确实解决了眼前的问题，但我们有一个相当大的遗留代码库，作为从 CF7 升级到 CF8 的一部分，我们实际上无法通过所有这些代码库来更新查询.

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.

即使我已经确定了一种失败的特定情况(并将其封装在 mxunit 测试中)——这并不意味着没有其他可能存在问题的领域.我真的很想有一个解决方案来消除 OALL8 错误，而不是围绕它进行编码.

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.

更新 2: 在与我们的 DBA 核实后，他已将名为 CURSOR_SHARING 的参数设置为 SIMILAR.Oracle 默认值为 EXACT.发生的情况是，当 ColdFusion 将查询交给执行时，Oracle 将所有文字值转换为绑定变量，这似乎使 ColdFusion 感到困惑.将设置转回 EXACT 可以让文字查询正常工作.

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.

更新 3: Oracle 终于向我们发布了 JDBC 的带外补丁.它被识别为 JDBC 错误.最新的驱动程序在最终更新时应该包含它.如果你有支持，你也可以通过他们的 TAR 系统请求补丁.

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.

推荐答案

那么...使用绑定变量?

So... use bind variables?

无论如何，您都应该使用它们(通过 cfqueryparam)来确保安全，如果它解决了问题，那就更有理由这样做了.

You should be using them (via cfqueryparam) for security anyway, and if it solves the problem that's even more reason to do so.

如果您对实际错误的含义感兴趣，Google 有 很多 的 results，暗示这是一个JDBC 驱动程序出错，甚至建议 补丁可用.

If you're interested in what the actual error means, Google has plenty of results, suggesting that it's an error with the JDBC driver, and even suggests a patch is available.

但是我在您的帖子中没有看到实际问题...?

But I don't see an actual question in your post...?

这篇关于Oracle:OALL8 处于不一致状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！