JDBC-ORACLE10g BUG

 [From]: http://www.thatisjava.com/jdbc/69759/

The actual problem I have is not to get the update counts.

After several test case scenarios, I found out some interesting facts.

1. As I said in the beginning of my thread, As per the the API, the executeUpdate() method returns either "the row count for INSERT, UPDATE, or DELETE statements or 0 for SQL statements that return nothing" .

The executeUpdate() would return the rowCount only for the PreparedStatement and Statement. Although the CallableStatement extends the executeUpdate() of the Statement Interface, this would not return a rowCount, even if the call to the procedure makes several updates/inserts in the database. The executeStatement() on the callableStatement would return 1 if there was an OUT parameter inside the called Procedure, else it would return 0.

2. We were writing our logic based on this return Value as below:

int primaryId = Obj.getPrimaryId();
  returnValue = p_CallStmt.executeUpdate();
  if(returnValue != 0){// If the procedure has an OUT Parameter primaryId = p_CallStmt.getInt(1);
 // This is the OUT Parameter returned by the Procedure}else{//if no OUT parameter, return a default key value} return primaryId;

3. Everything was fine with 9i Drivers. When no OUT parameter was returned, the executeUpdate() returned 0 and the control goes into the else loop returning default value.
In 10g JDBC Drivers, the executeUpdate() always returns 1. So even if there was no OUT parameter from the Procedure, the program still tries to get p_CallStmt.getInt(1) and throws a NullPointerException.

The solution I came up with was to register the OUT parameter even if you don't
have an OUT parameter. This would give us the required primaryId which we passed to the procedure. sometimes, if the primaryId gets changed in the procedure, it will be returned through the OUT parameter.