Java/Oracle：执行准备好的语句在循环的第二次迭代中失败（并非所有变量都绑定）。为什么？

发布于 2024-08-07 13:53:47 字数 1384 浏览 3 评论 0原文

我正在调试一个 Java 应用程序，它通过瘦客户端连接到 Oracle DB。

代码如下所示：（我试图简化这里的用例，所以如果 t 实际上没有编译，请原谅我）

Connection conn = myEnv.getDbConnection();
CallableStatement call = conn.prepareCall(
         "{ ? = call SomePackage.SomeFunction (?)}");
call.registerOutParameter(1, OracleTypes.CURSOR);
for (int runCount = 0; runCount <= 1; runCount++) {
    currency = getCurrency(runCount); // NOTE: [0]=CAD, [1]=USD
    call.setString(2, currency);
    try { call.execute(); } catch { // BREAKS HERE! }
    ResultSet rset = (ResultSet)call.getObject(1);
    ... more code that I think is irrelevant as it does not use/affect "call"
}

当我运行此代码时，会发生以下情况：

循环的第一次迭代，currency 设置为“CAN”。
循环的整个代码运行得很好。
循环的第二次迭代，货币设置为“USD”。
“execute()” 调用抛出 SQLException，如下所示：
ORA-01008: 并非所有变量都绑定

为什么？

我最初的怀疑是它某种程度上与循环之前的 registerOutParameter 调用有关，但在 2d 迭代中不会被调用。但是将该调用移至循环内并不能解决问题。似乎 execute() 调用取消了某些东西的绑定，但是在循环内同时进行这两个绑定并没有帮助。

我缺少什么？

如果这是显而易见的事情，请保持温和 - 我对 Oracle 和瘦客户端知之甚少，谷歌搜索无数花哨的查询没有返回任何喜欢。

另一条线索：当应用程序运行在带有 OCI 驱动程序的 Oracle 9 上时，这种设计似乎以前就有效。我调试它的原因是有人将它“升级”到 Oracle 10.2 thi 客户端，但它崩溃了。

我的下一步可能应该是将整个 CallableStatement 引入循环中，但这违背了为什么我首先使用准备好的语句的整个想法，不是吗？

原文

I'm debugging a Java App, which connects to Oracle DB via a thin client.

The code looks as follows: (i'm trying to simplify the use case here so pardon me if t does not actually comile)

Connection conn = myEnv.getDbConnection();
CallableStatement call = conn.prepareCall(
         "{ ? = call SomePackage.SomeFunction (?)}");
call.registerOutParameter(1, OracleTypes.CURSOR);
for (int runCount = 0; runCount <= 1; runCount++) {
    currency = getCurrency(runCount); // NOTE: [0]=CAD, [1]=USD
    call.setString(2, currency);
    try { call.execute(); } catch { // BREAKS HERE! }
    ResultSet rset = (ResultSet)call.getObject(1);
    ... more code that I think is irrelevant as it does not use/affect "call"
}

When I run this code, the following happens:

First iteration of the loop, currency is set to "CAN".
Entire code of the loop runs perfectly fine.
Second iteration of the loop,currency is set to "USD".
The "execute()" call throws SQLException, as follows:
ORA-01008: not all variables bound

Why?

My initial suspicion was that it somehow related to registerOutParameter call before the loop that doesn't get called on 2d iteration. But moving that call inside the loop does not fix the problem. It seems that execute() call un-binds something but having both bindings inside the loop does not help.

What am I missing?

If it's something obvious, please be gendle - I know very little about Oracle and thin client, and Googling witrh miriad of fancy queries returned no love.

One additional clue: this design seemed to have worked before when the app was on Oracle 9 with OCI drivers. The reason I'm debuggin it is someone "upgraded" it to Oracle 10.2 thi client and it broke.

My next step should probably be bringing in entire CallableStatement into the loop, but that kind of defeats the whole idea of why I though prepared statements are used in the first place, no?

分享到QQ

分享到微博