java.sql.SQLException: ORA-00928: missing SELECT keyword. when inserting record to DB using JDBC

Question

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.

Try without the quotes (should be double quotes if any), without the trailing semicolon, and with a space before values. — Thilo
– Thilo, Commented Nov 25, 2011 at 4:14
There's no space between RECORD_COUNT) and VALUES( .. so your insert command looks like 'INSERT into MY_TABLE(......)VALUES(????)` — Kal
– Kal, Commented Nov 25, 2011 at 4:20
I think you'll always need to use the double quotes, since DATE is normally an invalid identifier. (Which is why you shouldn't use it as a column name. And plus, why is it stored as a string?) — Jon Heller
– Jon Heller, Commented Nov 25, 2011 at 4:21
You actually don't need a space before values, although it normally looks better that way. — Jon Heller
– Jon Heller, Commented Nov 25, 2011 at 4:23
Remove the semicolon before the closing double quotes of the query. — One-One
– One-One, Commented Nov 25, 2011 at 4:30

KV Prajapati · Accepted Answer · 2011-11-25 05:00:22Z

5

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.

answered Nov 25, 2011 at 5:00

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chip Over a year ago

hey man, I was about to answer that here. yep, the date column need to be surrounded by double quotes and then escaped. thanks for the answer :D :D :D

Jomoos · Accepted Answer · 2011-11-25 10:30:07Z

4

I can spot two problems:

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.
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(?, ?, ?, ?, ?);";

edited Nov 25, 2011 at 10:30

answered Nov 25, 2011 at 5:05

Jomoos

13.1k10 gold badges57 silver badges92 bronze badges

1 Comment

KV Prajapati Over a year ago

Read the OP and title - It is all about oracle.

Jwalin Shah · Accepted Answer · 2011-11-25 04:43:40Z

2

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.

answered Nov 25, 2011 at 4:43

Jwalin Shah

2,5311 gold badge19 silver badges22 bronze badges

1 Comment

chip Over a year ago

yep, i tried inserting a record from the db ide and knew that there were some syntax errors. thanks for the answer jwalin. appreciate it

Ed Brannin · Accepted Answer · 2012-10-01 21:48:19Z

1

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
  (?, ?, ?, ?, ?, ?, ?, ?, ?)

answered Oct 1, 2012 at 21:48

Ed Brannin

7,9312 gold badges31 silver badges32 bronze badges

Comments

Vlad Mihalcea · Accepted Answer · 2016-04-27 08:58:31Z

1

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 (?, ?, ?)

answered Apr 27, 2016 at 8:58

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

Comments

Dean · Accepted Answer · 2015-02-06 16:59:29Z

0

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.

answered Feb 6, 2015 at 16:59

Dean

3214 silver badges12 bronze badges

Collectives™ on Stack Overflow

java.sql.SQLException: ORA-00928: missing SELECT keyword. when inserting record to DB using JDBC

6 Answers 6

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related