Hi
I believe I've stumbled on a bug in the Oracle 12c JDBC driver when executing the PreparedStatement.getParameterMetaData() method for a query that that contains a LIKE operator with a bind variable which causes java.sql.SQLSyntaxErrorException to be thrown with the error message "ORA-00904: invalid identifier". If I run the same code using the Oracle 11g JDBC driver the method executes without a problem, also if I replace the LIKE operator with the = operator whilst using the Oracle 12c JDBC driver the method executes without a problem.
To reproduce this problem I have used the following code:
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName(oracle.jdbc.OracleDriver);
Connection dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
//This SQL statement works
//String selectSQL = "select * from dual where dummy = ?";
//This SQL statement fails with ORA-00904: "DUMMYIKE": invalid identifier
String selectSQL = "select * from dual where dummy like ?";
PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
ParameterMetaData pmd = preparedStatement.getParameterMetaData();
preparedStatement.close();
dbConnection.close();
}
The stack trace generated is as follows:
Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00904: "DUMMYIKE": invalid identifier
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:392)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:385)
at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1018)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Odscrarr.doODNY(T4C8Odscrarr.java:96)
at oracle.jdbc.driver.T4CPreparedStatement.doDescribe(T4CPreparedStatement.java:717)
at oracle.jdbc.driver.OracleStatement.describe(OracleStatement.java:4404)
at oracle.jdbc.driver.OracleResultSetMetaData.<init>(OracleResultSetMetaData.java:52)
at oracle.jdbc.driver.OracleStatement.getResultSetMetaData(OracleStatement.java:4387)
at oracle.jdbc.driver.OraclePreparedStatement.getMetaData(OraclePreparedStatement.java:5581)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.getMetaData(OraclePreparedStatementWrapper.java:1509)
at oracle.jdbc.driver.OracleParameterMetaData.getParameterMetaData(OracleParameterMetaData.java:70)
at oracle.jdbc.driver.OraclePreparedStatement.getParameterMetaData(OraclePreparedStatement.java:12861)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.getParameterMetaData(OraclePreparedStatementWrapper.java:1551)
at example.App.main(App.java:25)
The only reference I can find to this issue is in this StackOverflow question (http://stackoverflow.com/questions/22629438/sqlsyntaxerrorexception-when-using-like-with-ojdbc7-jar) where one of the answers suggests that this is due to bug 18590786.
I am a bit surprised that after several hours searching I can only find a single reference to this issue as I would have expected more people to be experiencing the same issue.
Does anyone have any further information about this issue or the bug number 18590786 that they could share with me please?
The versions of the Oracle JDBC driver that seem to be affected are 12.1.0.1 and 12.1.0.2.
The versions of the Oracle JDBC driver that seem to be unaffected are 11.2.0.4 and earlier.
Kind Regards
Paul