PreparedStatement + Select for update + Oracle 12c + ORA-01461 in primary key column

Question

I have a strange problem when trying to execute a select for update statement and then execute subsequently an insert or update. I get an ORA-01461 exception. This happens only when using the latest ojdbc driver (12.1.0.2), while in the older one it works fine (12.1.0.1).

More specifically the latest driver seems to have some kind of limit in primary key character length (limited to 32 chars) although the corresponding column is declared more than 32 chars. Sample code to replicate the problem is the following:

CREATE TABLE "TEST_TABLE" (
 "TEST_ID" VARCHAR2(40 CHAR) NOT NULL ENABLE,
 "TEST_COMMENT" VARCHAR2(200 CHAR),
 CONSTRAINT "TEST_TABLE_PK" PRIMARY KEY ("TEST_ID")
);

And some java:

public class DemoUpdatableResultSet {

    private static final String DB_URL = "jdbc:oracle:thin:@xxxx:1521/xxxxx";
    private static final String DB_USER = "xxx";
    private static final String DB_PASS = "xxx";

  public static Connection getConnection() throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
    return conn;
  }

  public static void main(String[] args) {
    final String uuid = UUID.randomUUID().toString();
    ResultSet rs = null;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "SELECT t.* FROM TEST_TABLE t WHERE t.TEST_ID=? FOR UPDATE";
      pstmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_UPDATABLE);
      pstmt.setString(1, uuid); // set input values
      rs = pstmt.executeQuery(); // create an updatable ResultSet
                                               // insert column values into the insert row.
      rs.moveToInsertRow();                    // moves cursor to the insert row
      rs.updateString("TEST_ID", uuid);           // updates the 2nd column
      rs.updateString("TEST_COMMENT", "Comment for: " + uuid);
      rs.insertRow();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        rs.close();
        pstmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

First line of main method creates a uuid

 UUID.randomUUID().toString();

which is 36 chars long. Running this sample class will produce an ORA-01461 error, but changing the line mentioned above to

 UUID.randomUUID().toString().replaceAll("-", "");

which yields to 32 chars string will run correctly and insert the row in the database. Notice that the "TEST_ID" column in which the above string is saved is VARCHAR2(40 CHAR) and can accommodate both 32 and 36 chars strings. Increasing the column's length to even greater numbers won't change anything.

I hope my sample code is easy to read and understand and I am looking forward for a solution/explanation to this problem.

Thanks!

Database Info:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Modified slightly to run an insert statement with the same data to show that this problem is more weird than it seems (regarding the uuid string length). The following sample code executes correctly with the newest oracle driver:

  public static void main(String[] args) {
    final String uuid = UUID.randomUUID().toString();
    ResultSet rs = null;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "INSERT INTO TEST_TABLE (TEST_ID, TEST_COMMENT) VALUES (?, ?)";
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, uuid); // set input values
      pstmt.setString(2, "Comment for: " + uuid); // set input values
      rs = pstmt.executeQuery();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        rs.close();
        pstmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

Answer 1

Opened an SR to oracle since this is a bug of 12.1.0.2.0 jdbc driver and would need a patch to resolve it.