JDBC 11.2.0.1.0 Throws "java.sql.SQLException: Invalid column index" When "NORDER" is Used As a Bind Variable (Doc ID 1092429.1)

Last updated on MAY 04, 2017

Applies to:

JDBC - Version 11.2.0.1.0 to 11.2.0.1.0 [Release 11.2]
Information in this document applies to any platform.

Symptoms

JDBC 11.2.0.1.0 THIN driver throws the following exception when an SQL statement contains the bind variable (table column) with the name "NORDER"
Exception in thread "Main Thread" java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setIntInternal(OraclePreparedStatement.java:4724)
at oracle.jdbc.driver.OraclePreparedStatement.setInt(OraclePreparedStatement.java:4711)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setInt(OraclePreparedStatementWrapper.java:198)
at BindTest.main(BindTest.java:18)

However, the same code was found to work fine with JDBC 11.1.0.6 / 11.1.0.7

Testcase:
First create a table with some data:
CREATE TABLE SXML_ELEMENT ("NORDER" NUMBER(9,0),"XML_ELEMENT_KEY" NUMBER(9,0));
INSERT INTO SXML_ELEMENT values (1,123);
INSERT INTO SXML_ELEMENT values (2,456);
COMMIT;
select * from SXML_ELEMENT;
And then, run this code:
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
 
public class BindTest {
  static String userid="username";
  static String password = "password";
  static String url = "jdbc:oracle:thin:@localhost:1521:XE";
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection con = DriverManager.getConnection(url, userid, password);
    PreparedStatement ps = con.prepareStatement("SELECT * FROM SXML_ELEMENT WHERE NORDER= ? AND XML_ELEMENT_KEY = ?");
 
    //breakpoint on next line and look at ps.preparedStatement.numberOfBindPositions
    ps.setInt(1, 1);
    ps.setInt(2, 123);  //this line will fail with the new driver because 'ps' thinks there is only 1 bind variable
    ResultSet rs = ps.executeQuery();
 
    rs.next();
    System.out.println("Result set: "+rs.getInt(1)+","+rs.getInt(2));
  }
}

Changes

Upgrade to JDBC 11.2.0.1.0 / 11.2.0.2.0

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms