java.sql.SQLException: ORA-00928: missing SELECT k

问题:

I get an error when I try to insert some rows to a db. so here is the code

try {
    String insertStmt = "INSERT into " +
                        "MY_TABLE('RECORD_TYPE', 'FILE_TYPE', 'DATE', 'BATCH_NO', 'RECORD_COUNT')" +
                        "VALUES(?, ?, ?, ?, ?);";

    PreparedStatement pstmt = super.con.prepareStatement(insertStmt);

    pstmt.setString(1, input[0]);
    pstmt.setString(2, input[1]);
    pstmt.setString(3, input[2]);
    pstmt.setString(4, input[3]);
    pstmt.setString(5, input[4]);

    System.out.println("Insert rows : " + pstmt.executeUpdate());

} catch (SQLException sqle) {
    System.out.println(sqle.getMessage());
    sqle.printStackTrace();
} catch (Exception e) {
    System.out.println(e.getMessage());
    e.printStackTrace();
} finally {
    con.close();
}

and everything on the db is of varchar type, double checked the columns (they all are the same name), took out the quotes off the column name (same result) no success. to add it up, the error message is not very helpful.

any suggestions would be appreciated.

回答1:

I can spot two problems:

1. No need for single quotes around column names. But you may wrap it in double quotes. It is necessary if you are using reserved keywords for column names or table names. Here DATE.
2. You need a space before VALUES.

So you need to change insertStmt to somthing like this:

String insertStmt = "INSERT into " +
    "MY_TABLE(RECORD_TYPE, FILE_TYPE, \"DATE\", BATCH_NO, RECORD_COUNT) " +
    "VALUES(?, ?, ?, ?, ?);";

回答2:

You need to change the SQL statement. (Never use reserved words as identifiers)

String insertStmt = "INSERT into \"MY_TABLE\" (RECORD_TYPE,FILE_TYPE, 
              \"DATE\",BATCH_NO,RECORD_COUNT) VALUES (?, ?, ?, ?, ?)";

Use " (double quotes) to escape the reserved words/keywords.

回答3:

Print insertStmt String in Console and try to fire it in directly backend. It gives you exact error in backend. It seens some spacing or syntax error.

回答4:

I just came to this page while searching for ORA-00928, and I'd like to note that my problem was an extra comma at the start of the column list:

INSERT INTO redacted.redacted
  (
  , redacted_id   -- The comma at the start of this line will trigger ORA-00928.
  , another_redacted_id
  , redacted1
  , redacted2
  , redacted3
  , created_at
  , created_by
  , changed_at
  , changed_by
  )
  VALUES
  (?, ?, ?, ?, ?, ?, ?, ?, ?)

回答5:

For others searching for the same error: Other syntactical issues with the query can cause the same exception to be thrown. For example, omitting the word VALUES.

回答6:

I was running the same issue, and in my case the query was like this:

insert into Address (number, street, id) values (?, ?, ?)

The problem was caused by the number column name since number is a reserved keyword in Oracle, and the exception was sort of misleading ORA-00928: missing SELECT keyword.

After escaping the number column, the statement was executed normally:

insert into Address ("number", street, id) values (?, ?, ?)