Issue with re-using Oracle Style Bind Variables causes ORA-17041 with JDBC 10g and 11g Drivers (Doc ID 731833.1)

Last updated on AUGUST 25, 2017

Applies to:

JDBC - Version 10.2.0.3 and later
Information in this document applies to any platform.
***Checked for relevance on 14-Jan-2012***


Symptoms

When using JDBC 10g or 11g drivers,  Oracle style binding that uses the same bind variable twice does not automatically load the same value for the second bind resulting in an ORA-17041 error. This is not an issue in JDBC 9i drivers.

Testcase:

import java.sql.*;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OraclePreparedStatement;

public class Class1 {
public static void main(String[] args) throws SQLException {
Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement("select * from dept where deptno = :v_deptno and deptno = :v_deptno");

ps.setInt(1,10);

ResultSet rs = ps.executeQuery();
rs.next();
System.out.println(rs.getString(2));
conn.close();
}

public static Connection getConnection() throws SQLException {
String username = "<USERNAME>";
String password = "<PASSWORD>";
String thinConn = "jdbc:oracle:thin:@//host:1521/ORCL";
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(thinConn,username,password);
conn.setAutoCommit(false);
return conn;
}

}

JDBC version 9.2.0.8 result:

ACCOUNTING

JDBC version 10.1.0.5 or 10.2.0.4 or 11.1.0.6 results:

Exception in thread "main" java.sql.SQLException: Missing IN or OUT parameter at index:: 2
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1729)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3365)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3415)
at Class1.main(Class1.java:12)

Changes

 

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