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 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]

Bind Variables in PL/SQL

Bind Variables in PL/SQL

variables in PL/SQL are implicitly bind variables.

 create or replace procedure dsal(p_empno in number)
    as
      begin
        update emp set sal=sal*2 where empno = p_empno;
        commit;
      end;
    /    

Now you might be thinking that you've got to replace the p_empno with a bind variable. However, the good news is that every reference to a PL/SQL variable is in fact a bind variable.

Dynamic SQL

In fact, the only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL.

Dynamic SQL, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. For next example would always require a hard parse when it is submitted:

 create or replace procedure dsal(p_empno in number)
    as
      begin
        execute immediate
         'update emp set sal = sal*2 where empno = '||p_empno;
      commit;
      end;
    /   

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:

    create or replace procedure dsal(p_empno in number)
    as
      begin
        execute immediate
         'update emp set
         sal = sal*2 where empno = :x' using p_empno;
      commit;
      end;
    /

And that's all there is to it. One thing to bear in mind, though, is that you can't substitute actual object names (tables, views, columns etc) with bind variables - you can only subsitute literals. If the object name is generated at runtime, you'll still need to string concatenate these parts, and the SQL will only match with those already in the shared pool when the same object name comes up. However, whenever you're using dynamic SQL to build up the predicate part of a statement, use bind variables instead and you'll reduce dramatically the amount of latch contention going on.

The Performance Killer

Just to give you a tiny idea of how huge of a difference this can make performance wise, you only need to run a very small test:

Here is the Performance Killer ....

SQL> alter system flush shared_pool;
SQL> set serveroutput on;

    declare
          type rc is ref cursor;
          l_rc rc;
          l_dummy all_objects.object_name%type;
          l_start number default dbms_utility.get_time;
      begin
          for i in 1 .. 1000
          loop
              open l_rc for
              'select object_name
                 from all_objects
                where object_id = ' || i;
              fetch l_rc into l_dummy;
              close l_rc;
              -- dbms_output.put_line(l_dummy);
          end loop;
          dbms_output.put_line
           (round((dbms_utility.get_time-l_start)/100, 2) ||
            ' Seconds...' );
      end;
    /

Modified code

    declare
          type rc is ref cursor;
          l_rc rc;
          l_dummy all_objects.object_name%type;
          l_start number default dbms_utility.get_time;
      begin
          for i in 1 .. 1000
          loop
              open l_rc for
              'select object_name
                 from all_objects
                where object_id = :x'
              using i;
              fetch l_rc into l_dummy;
              close l_rc;
              -- dbms_output.put_line(l_dummy);
          end loop;
          dbms_output.put_line
           (round((dbms_utility.get_time-l_start)/100, 2) ||
            ' Seconds...' );
    end;
    /

Modified code runs faster than the earlier version. This is because it reduces the number of parses required. This is good for scalability as less cpu and memory is consumed.