Java/Oracle: executing a prepared statement fails on a second iteration of a loop (not all variables bound). Why?

Question

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?

Answer 1

Have you tried adding call.clearParameters() into the loop? Perhaps it would reset some internal state on the object that it needs to execute again.

Answer 2

The explanation obtained via Oracle Support call was that this version of Java (1.3) was not compatible with new Oracle. Java 1.4 fixed the issue.