Query runs fine in SQL Developer but gives Error Msg = ORA-00905: missing keyword in JDBC

Question

I am trying to run this SQL prepared statement from code.

select
    COUNT(*)
from table1 ed, table2 e
where ed.id = e.id
  and e.status_cd = ?
  and ed.active_ind = 1
  and {in}
  and systimestamp < FROM_TZ(cast(ed.end_effective_dt_tm as TIMESTAMP), ?)
  and FROM_TZ(cast(? as TIMESTAMP), ?) between TIMESTAMP ? and TIMESTAMP ?

The JDBC code that reads this and executes against an Oracle database reads something like this :

int parameterIndex = 0;
      stmt.setDouble(
          ++parameterIndex, doubleValue);
stmt.setInClause(
          ++parameterIndex, inClauseColumns.toArray(new InClauseColumn[inClauseColumns.size()]));
      stmt.setString(
          ++parameterIndex, tz_Id); /* Setting time zone for casting ed.end_effective_dt_tm */
      stmt.setString(++parameterIndex, timeFilterColumn);
      stmt.setString(++parameterIndex, tz_Id); // timeFilter
      stmt.setTimestamp(
          ++parameterIndex, new Timestamp(startTime), calculationTimeZone); // startTime
      stmt.setTimestamp(++parameterIndex, new Timestamp(endTime), calculationTimeZone); // endTime
      ResultSet rs = null;
      try {
        while (stmt.hasNext()) {
          rs = stmt.next();
   // do stuff

This gives the following error in JDBC :

Error Msg = ORA-00905: missing keyword

But the same query when run from SQL developer returns the expected rows from the database.

Example query ran from SQL developer :

select
    COUNT(*)
from table1 ed, table2 e
where ed.id = e.id
  and e.status_cd = 854 /*Prameter 1*/
  and ed.active_ind = 1
  and ed.facility_cd in (1.7241194E7) /*in clause parameter 2 */
  and
  systimestamp < FROM_TZ(cast(ed.end_effective_dt_tm as TIMESTAMP), 'America/Chicago' /*parameter 3 */)
  and
  FROM_TZ(cast(e.updt_dt_tm /*parameter 4 */ as TIMESTAMP), 'America/Chicago') /*parameter 5 */
  between
  TIMESTAMP '2021-06-30 02:23:20.0' /*parameter 6 */
  and TIMESTAMP '2021-11-10 18:09:24.774' /*parameter 7 */

Can someone please provide some suggestion on how to make this work with JDBC? I can't seem to figure out the issue here. Thanks.

Answer 1

Query problems:

• incorrect in clause and {in} just ed.facility_cd in ( ? ) and setting parameter for each array element
• in case setting specific java.sql.Timestamp type into JDBC statement you do not need to perform parameter cast in the query between TIMESTAMP ? and TIMESTAMP ? just between ? and ?

DB structure:

CREATE TABLE TABLE1 (
 ID NUMBER,
 active_ind NUMBER,
 end_effective_dt_tm TIMESTAMP,
 facility_cd FLOAT
);

CREATE TABLE TABLE2 (
 ID NUMBER,
 status_cd FLOAT,
 updt_dt_tm TIMESTAMP
);

Example of working JDBC statement:

    public Long execute(Connection connection) throws SQLException {
        long count = 0L;

        double doubleValue = 854D;
        Double[] inClauseValues = new Double[]{1.7241194E7, 1.7241194E8};
        String tz_Id = "America/Chicago";
        Timestamp startTime = Timestamp.valueOf("2021-06-30 02:23:20.0");
        Timestamp endTime = Timestamp.valueOf("2021-11-10 18:09:24.774");
        String timeFilterColumn = "e.updt_dt_tm";
        String inClauseColumn = "ed.facility_cd";

        String sqlQuery = " select COUNT(*) " +
                " from table1 ed, table2 e " +
                "  where ed.id = e.id " +
                "  and e.status_cd = ? " +
                "  and ed.active_ind = 1 " +
                "  and ? in ( " + Arrays.stream(inClauseValues).map(v -> "?").collect(Collectors.joining(", ")) + " ) " +
                "  and systimestamp < FROM_TZ(cast(ed.end_effective_dt_tm as TIMESTAMP), ? ) " +
                "  and FROM_TZ(cast( ? as TIMESTAMP), ? ) between ? and ? ";

        try (PreparedStatement stmt = connection.prepareStatement(sqlQuery)) {
            int parameterIndex = 0;

            stmt.setDouble(++parameterIndex, doubleValue); // Setting e.status_cd
            stmt.setString(++parameterIndex, inClauseColumn); //Set dynamic column for in cluase
            for (Double value : inClauseValues) { //Setting ed.facility_cd in
                stmt.setDouble(++parameterIndex, value);
            }
            stmt.setString(++parameterIndex, tz_Id); /* Setting time zone for casting ed.end_effective_dt_tm */
            stmt.setString(++parameterIndex, timeFilterColumn); //Setting timeFilterColumn e.updt_dt_tm
            stmt.setString(++parameterIndex, tz_Id); /* Setting time zone for casting e.updt_dt_tm  */
            stmt.setTimestamp(++parameterIndex, startTime); // startTime
            stmt.setTimestamp(++parameterIndex, endTime); // endTime

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    count = rs.getLong(1);
                }
            }
        }
        return count;
    }

UPDATE
Another solution, you can prebuild query string with dynamic columns:

    public Long execute(Connection connection) throws SQLException {
        long count = 0L;

        double doubleValue = 854D;
        Double[] inClauseValues = new Double[]{1.7241194E7, 1.7241194E8};
        String tz_Id = "America/Chicago";
        Timestamp startTime = Timestamp.valueOf("2021-06-30 02:23:20.0");
        Timestamp endTime = Timestamp.valueOf("2021-11-10 18:09:24.774");
        String timeFilterColumn = "e.updt_dt_tm";
        String inClauseColumn = "ed.facility_cd";

        String sqlQuery = " select COUNT(*) " +
                " from table1 ed, table2 e " +
                "  where ed.id = e.id " +
                "  and e.status_cd = ? " +
                "  and ed.active_ind = 1 " +
                "  and " + inClauseColumn + " in ( " + Arrays.stream(inClauseValues).map(v -> "?").collect(Collectors.joining(", ")) + " ) " +
                "  and systimestamp < FROM_TZ(cast(ed.end_effective_dt_tm as TIMESTAMP), ? ) " +
                "  and FROM_TZ(cast( " + timeFilterColumn + " as TIMESTAMP), ? ) between ? and ? ";

        try (PreparedStatement stmt = connection.prepareStatement(sqlQuery)) {
            int parameterIndex = 0;

            stmt.setDouble(++parameterIndex, doubleValue); // Setting e.status_cd
            for (Double value : inClauseValues) { //Setting ed.facility_cd in
                stmt.setDouble(++parameterIndex, value);
            }
            stmt.setString(++parameterIndex, tz_Id); /* Setting time zone for casting ed.end_effective_dt_tm */
            stmt.setString(++parameterIndex, tz_Id); /* Setting time zone for casting e.updt_dt_tm  */
            stmt.setTimestamp(++parameterIndex, startTime); // startTime
            stmt.setTimestamp(++parameterIndex, endTime); // endTime

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    count = rs.getLong(1);
                }
            }
        }
        return count;
    }