The problem here lies in using the unsupported bind variable placeholders. According to Oracle the fact that it works with previous version of JDBC driver is a merer accident and not the expected behavior. If it's not possible to modify the code the easiest option to remedy to the situation is to downgrade the JDBC driver to 11.1. As the issue comes as a result of the JDBC driver not because of the database
Java code given at the end of the post could be used to test the unsupported syntax against various databases and JDBC drivers. Following table list summary of findings from running the test code with 10.2/11.1/11.2 JDBC drivers and databases.
| JDBC Driver Version | Database Version | "?1" Works |
|---|---|---|
| 10.2.0.5 | 10.2.0.5 | YES |
| 10.2.0.5 | 11.1.0.7 | YES |
| 10.2.0.5 | 11.2.0.3 | YES |
| 11.1.0.7 | 10.2.0.5 | YES |
| 11.1.0.7 | 11.1.0.7 | YES |
| 11.1.0.7 | 11.2.0.3 | YES |
| 11.2.0.3 | 10.2.0.5 | NO |
| 11.2.0.3 | 11.1.0.7 | NO |
| 11.2.0.3 | 11.2.0.3 | NO |
Create and populate table used in the test code
SQL> create table x (a varchar2(10), b number);
Table created.
SQL> begin
2 for i in 1 .. 20
3 loop
4 insert into x values('abc'||i,i);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.Test code
OracleDataSource ds = new OracleDataSource();
ds.setUser("asanga");
ds.setPassword("asa");
ds.setURL("jdbc:oracle:thin:@192.168.0.66:1521:ent11g2");
Connection con = ds.getConnection();
DatabaseMetaData meta = con.getMetaData();
System.out.println("Driver Name " + meta.getDriverName());
System.out.println("Driver Version " + meta.getDriverVersion());
System.out.println("Driver Major Version " + meta.getDriverMajorVersion());
System.out.println("Driver Minor Version " + meta.getDriverMinorVersion());
System.out.println("Database Major Version " + meta.getDatabaseMajorVersion());
System.out.println("Database Minor Version " + meta.getDatabaseMinorVersion());
System.out.println("Database Product Name " + meta.getDatabaseProductName());
System.out.println("Database Product Version " + meta.getDatabaseProductVersion());
String SQL = "select * from x where b = ?1"; // works on 10gR2, 11gR1 jdbc driver but not on 11gR2 jdbc driver
// String SQL = "select * from x where b = ?"; // works on all drivers
PreparedStatement pr = con.prepareStatement(SQL);
pr.setInt(1, 10);
ResultSet rs = pr.executeQuery();
while(rs.next()){
System.out.println(rs.getString("A")+" "+rs.getInt("B"));
}
rs.close();
pr.close();
con.close();Useful metalink notes
ORA-00933 When Using Bind Variables in JDBC 11.2 [ID 1304235.1]
