Tuesday, May 14, 2013

Unsupported Bind Variable Syntax in PreparedStatments and 10.2/11.1/11.2 JDBC Drivers

Oracle supports two kind of bind variable placeholder syntax in Java PreparedStatments. A bind variable placeholder could be denoted with a colon and a number ":1" or with a question mark "?". However there are situation where developers may use a syntax such as question mark followed by a number "?1". Though this syntax is not supported in Oracle, when using JDBC drivers 10.2 and 11.1 the Java code would execute without an error and returns results as expected. But with 11.2 JDBC driver this will give the error ORA-00933: SQL command not properly ended and java code would not execute as before. So as part of database upgrade if JDBC driver is also upgraded and if java code has the syntax similar to "?1" etc then code that previously worked would not work anymore.
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 VersionDatabase Version"?1" Works
10.2.0.510.2.0.5YES
10.2.0.511.1.0.7YES
10.2.0.511.2.0.3YES
11.1.0.710.2.0.5YES
11.1.0.711.1.0.7YES
11.1.0.711.2.0.3YES
11.2.0.310.2.0.5NO
11.2.0.311.1.0.7NO
11.2.0.311.2.0.3NO




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]