getParameterMetaData() throws java.sql.SQLSyntaxErrorException: ORA-00904: "F": invalid identifier

Question

I have written a simple program in java, which creates connection to Oracle database and executes update query.

The query gets executed successfully, but if the update query contains a column starting with "F" then prepareStatement.getParameterMetaData() throws exception

"java.sql.SQLSyntaxErrorException: ORA-00904: "F": invalid identifier".

If I remove the column starting with "F" then prepareStatement.getParameterMetaData() executes correctly.

My configruation is,

Oracle: 12.1.0.2

JDK: 1.8

ojdbc driver: ojdbc7.jar (included in 12.1.0.2)

I found the same issue with ojdbc6.jar as well.

Is there any issue with the driver?

Code:

public class TestDriver {

public static void main(String args[]) {
    String sql = "UPDATE test SET test1 = ?, Fun=? WHERE test2 = ?";
    PreparedStatement ppt = null;
    Connection connection = null;
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        connection = DriverManager.getConnection(
            "jdbc:oracle:thin:@127.0.0.1:1521/pdborcl2","oracleTrunk","oracleTrunk");
     ppt = connection.prepareStatement(sql);

    for(int i=0; i<1;i++) {
    ppt.setString(1, null);
    ppt.setString(2, null);
    ppt.setString(3, "1");
    ppt.executeUpdate();
    System.out.println("MSG "+ppt.getParameterMetaData());
    }
    }catch(Exception e) {
        System.out.println("e  "+e);
    } finally {
        try {
            ppt.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            connection.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}

score 0 · Answer 1 · answered Jan 30 '16 at 15:20

This really sounds like a bug to me, since the PreparedStatement works as expected. However, I found a workaround:

If you use quotes to define your columns, the ppt.getParameterMetaData() does not throw an exception any more.

So you need to write

String sql = "UPDATE test SET test1 = ?, \"FUN\"=? WHERE test2 = ?";

Keep in mind that when using double quotes to define names in Oracle you have to be case sensitive. Thus it is \"FUN\" and not \"Fun\" because all names are converted to uppercase when used without quotes.

BTW: my test setup was: `Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production`, `JDBC 12.1.0.1.0` and `Oracle JDK build 1.8.0_66-b17` — Michael Schaefers, Jan 30 '16 at 15:23

getParameterMetaData() throws java.sql.SQLSyntaxErrorException: ORA-00904: "F": invalid identifier

1 Answers1