Missing IN or OUT parameter at index:: 5, jdbc and oracle in a java application

Question

Altough I have only 4 placeholders, I am getting the error Missing IN or OUT parameter at index:: 5 below is the jpql and the method I am using the setParameters.

private static final String TIMESTAMP_FORMAT = "'ddmmyyyy hh24:mi:ss'";

JPQL

private static final String SQL_GET_EOD_LIST = "SELECT tr.*, txn.accounting_code, txn.accounting_explanation, txn.is_financial, "
        + "txn.send_to_statement, txn.affected_limit, txn.FINANCIAL_EFFECT "
        + "FROM (SELECT   app_id, ch_id, NVL(str_id, 'MAIN') AS str_id, trn_desc, trn_type, bal_code, "
        + "                 SUM (dedicated_money) AS balance, NVL(SUM(str_fee), 0) AS fee "
        + "            FROM WALLET.shopping_trn "
        + "           WHERE create_date BETWEEN TO_DATE (?, " + TIMESTAMP_FORMAT + ") "
        + "                                 AND TO_DATE (?, " + TIMESTAMP_FORMAT + ") "
        + "        GROUP BY app_id, ch_id, str_id, trn_desc, trn_type, bal_code) tr, "
        + "       WALLET.txn_def_lookup trn_lookup, "
        + "       WALLET.txn_def txn "
        + " WHERE tr.trn_desc = trn_lookup.trn_desc "
        + "   AND tr.bal_code = trn_lookup.bal_code "
        + "   AND trn_lookup.txn_def_id = txn.ID "
        + "   AND tr.balance > 0  "
        + "UNION ALL "
        + "SELECT tr.*, txn.accounting_code, txn.accounting_explanation, txn.is_financial, "
        + "       txn.send_to_statement, txn.affected_limit, txn.FINANCIAL_EFFECT "
        + "FROM (SELECT   app_id, ch_id, NVL(str_id, 'MAIN') AS str_id, trn_desc, trn_type, bal_code, "
        + "                 SUM (main_money) AS balance, NVL(SUM(str_fee), 0) AS fee "
        + "            FROM WALLET.shopping_trn "
        + "           WHERE create_date BETWEEN TO_DATE (?, " + TIMESTAMP_FORMAT + ") "
        + "                                 AND TO_DATE (?, " + TIMESTAMP_FORMAT + ") "
        + "        GROUP BY app_id, ch_id, str_id, trn_desc, trn_type, bal_code) tr, "
        + "       WALLET.txn_def_lookup trn_lookup, "
        + "       WALLET.txn_def txn "
        + " WHERE tr.trn_desc = trn_lookup.trn_desc "
        + "   AND tr.bal_code = trn_lookup.bal_code "
        + "   AND trn_lookup.txn_def_id = txn.ID "
        + "   AND tr.balance > 0  ";

the method

@Override
public List<EodView> fetchEodData(final String targetDate, String logInfo) {
    List<EodView> results;
    final String startDate = targetDate + " 00:00:00";
    final String endDate = targetDate + " 23:59:59";
    String query = obtainEodDataQuery();
    
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("fetchEodData SQL: {}",
                EMoneyCorpUtil.sqlBindReplace4Log(LOGGER.isDebugEnabled(),
                        query, startDate, endDate));
    }
    
    results = getJdbcTemplate().query(query, ps -> {
        ps.setString(1, startDate);
        ps.setString(2, endDate);
        ps.setString(3, startDate);
        ps.setString(4, endDate);
    }, new EodRowMapper());
    
    return results;
}

I tried spacing the placeholders, changing the syntax of the sql query, I have read many questions but none of it answered my problem.

Thank you.

Answer 1

1. In:

   if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("fetchEodData SQL: {}",
                EMoneyCorpUtil.sqlBindReplace4Log(LOGGER.isDebugEnabled(),
                        query, startDate, endDate));
   }
   

   You pass two bind parameters into the query when there are four placeholders that need bind values in your query. That may be the cause of the error.

2. You can pass LocalDateTime values rather than strings:

   DateTimeFormatter formatter = DateTimeFormatter.ofPattern("ddMMyyyy HH:mm:ss");
   results = getJdbcTemplate().query(query, ps -> {
        ps.setObject(1, LocalDateTime.parse(startDate, formatter));
        ps.setObject(2, LocalDateTime.parse(endDate, formatter));
    }, new EodRowMapper());
   

3. Your query repeats itself in the almost-identical sub-queries that you UNION together. You can use one query and UNPIVOT (untested as I do not have your tables or data):

   SELECT tr.app_id,
          tr.ch_id,
          tr.str_id,
          tr.trn_desc,
          tr.trn_type,
          tr.bal_code,
          tr.balance,
          tr.fee,
          txn.accounting_code,
          txn.accounting_explanation,
          txn.is_financial,
          txn.send_to_statement,
          txn.affected_limit,
          txn.FINANCIAL_EFFECT
   FROM   (
            SELECT app_id,
                   ch_id,
                   COALESCE(str_id, 'MAIN') AS str_id,
                   trn_desc,
                   trn_type,
                   bal_code,
                   COALESCE(SUM(dedicated_money), 0) AS dedicated_money,
                   COALESCE(SUM(main_money), 0) AS main_money
                   COALESCE(SUM(str_fee), 0) AS fee
            FROM   WALLET.shopping_trn
            WHERE  create_date BETWEEN ? AND ?
            GROUP BY
                   app_id,
                   ch_id,
                   str_id,
                   trn_desc,
                   trn_type,
                   bal_code
          )
          UNPIVOT (
            balance FOR balance_type IN (dedicated_money, main_money)
          ) tr
          INNER JOIN WALLET.txn_def_lookup trn_lookup
          ON     tr.trn_desc = trn_lookup.trn_desc
             AND tr.bal_code = trn_lookup.bal_code
          INNER JOIN WALLET.txn_def txn
          ON trn_lookup.txn_def_id = txn.ID
   WHERE  tr.balance > 0
   

   Then you only need to read the tables once and there are only two bind parameters.