Updating Resultset With ROWNUM and ORDER BY Fails With 'Invalid Column Index' (Doc ID 1667827.1)

Last updated on MARCH 08, 2017

Applies to:

JDBC - Version 11.2.0.1 to 12.1.0.1.0 [Release 11.2 to 12.1]
Information in this document applies to any platform.

Symptoms

When using the 11g or 12.1.0.1 JDBC Thin driver, running a program that uses an updatable resultset by performing a SELECT FOR UPDATE fails with the following error:

java.sql.SQLException: Invalid column index
  at oracle.jdbc.driver.OraclePreparedStatement.setROWIDInternal(OraclePreparedStatement.java:5851)
  at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8737)
  at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8286)
  at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9060)
  at oracle.jdbc.driver.OraclePreparedStatement.setOracleObject(OraclePreparedStatement.java:9078)
  at oracle.jdbc.driver.UpdatableResultSet.prepareCompareSelfBinds(UpdatableResultSet.java:4258)
  at oracle.jdbc.driver.UpdatableResultSet.prepareUpdateRowBinds(UpdatableResultSet.java:4150)
  at oracle.jdbc.driver.UpdatableResultSet.updateRow(UpdatableResultSet.java:2967)
  at TestJDBC.main(TestJDBC.java:71)

The resultset is set to: ResultSet.TYPE_SCROLL_SENSITIVE and ResultSet.CONCUR_UPDATABLE.

The query contains an ORDER BY clause.
If the ORDER BY clause is omitted, the error does not occur.

However, unlike the problem being investigated in Bug 10151295 (JAVA.SQL.SQLEXCEPTION: INVALID COLUMN INDEX USING BIND VAR IN ORDER BY), there are no bind variables in the ORDER BY clause.
Instead, there is a bind variable in a WHERE clause following the ORDER BY clause.
This is a bind against the ROWNUM pseudocolumn.

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