SQLSyntaxErrorException when using LIKE with ojdbc7.jar

Question

I have the following statement :

 PreparedStatement prpStat = conn
                    .prepareStatement("SELECT * FROM natperson WHERE name LIKE ?");
            prpStat.setString(1, "A");
            ParameterMetaData pmd = prpStat.getParameterMetaData();
            ResultSet rs = prpStat.executeQuery();

and i get the following excepting when i execute the the prpStat.getParameterMetaData(); method with ojdbc7.jar. The exception is not thrown when using ojdbc6.

java.sql.SQLSyntaxErrorException: ORA-00904: "NAMEIKE": ungültiger Bezeichner

    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:938)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
    at oracle.jdbc.driver.T4C8Odscrarr.doODNY(T4C8Odscrarr.java:96)
    at oracle.jdbc.driver.T4CPreparedStatement.doDescribe(T4CPreparedStatement.java:719)
    at oracle.jdbc.driver.OracleStatement.describe(OracleStatement.java:4223)
    at oracle.jdbc.driver.OracleResultSetMetaData.<init>(OracleResultSetMetaData.java:52)
    at oracle.jdbc.driver.OracleStatement.getResultSetMetaData(OracleStatement.java:4206)
    at oracle.jdbc.driver.OraclePreparedStatement.getMetaData(OraclePreparedStatement.java:4603)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.getMetaData(OraclePreparedStatementWrapper.java:1510)
    at oracle.jdbc.driver.OracleParameterMetaData.getParameterMetaData(OracleParameterMetaData.java:70)
    at oracle.jdbc.driver.OraclePreparedStatement.getParameterMetaData(OraclePreparedStatement.java:11621)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.getParameterMetaData(OraclePreparedStatementWrapper.java:1552)
    at com.agfa.jdbc.test.JDBCTester.main(JDBCTester.java:45)

Any ideas how to solve this?

Monody

ORA-00904 is invalid identifier. It's saying it doesn't recognize NAMEIKE that I don't see in your query – Ohg

i know..the problem is that it ignores/deletes the 2 chars after the name "name" when the prpStat.getParameterMetaData(); is called. – Monody

Answer 1

An Oracle Bug addresses the issue where the LIKE operator causes a problem in the parser:
Bug 18590786

This bug was also referenced in a similar Oracle Bug:
Bug 18644587 : ORACLEPARAMETERMETADATAPARSER GENERATES BAD STATEMENT FOR A UNION

It is not clear whether the fix is generally available yet.

Yonit

The bug appears to be fixed in the Oracle Database 12c Release 2 (12.2.0.1) JDBC driver and also isn't present in any of the Oracle Database 11g JDBC drivers. Only versions 12.1.0.1 and 12.1.0.2 seem to be affected. – Dissolute

Answer 2

Maybe a little too late, but a workaround to this is to qualify the field before the LIKE with double quotes like below:

SELECT * FROM natperson WHERE "NAME" LIKE ?

Gaff