0

I want to insert date into my table column "updatedDate" which has date as column data type. I am using spring jdbctemplate for data persistence. I tried by passing column value as java.util.Date, java.sql.Date and also java.sql.Timestamp, but none of them is working. I am getting below error :

java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected

Below is my code for insert.

public void insert(
        Hashtable<String, UploadError> errorCollection) {
    try {
        Set<String> keys = errorCollection.keySet();
        for (String key : keys) {
            UploadError contractUploadVO = errorCollection.get(key);
            String contract_error_sql = "insert into TARIFF_LOG("
                    + "ERR_MSG, JOB_ID, CRTD_DTT, CRTD_BY, Regn, UPDT_BY, UPDT_DTT)  "
                    + "values(:errorMsg, :jobId, :createdDate, :createUser, :region, :updatedBy, :updatedDate)";
            Map<String, Object> paramMap = new HashMap<String, Object>();
            paramMap.put("errorMsg", contractUploadVO.getErrMsgSearch());
            paramMap.put("jobId", contractUploadVO.getJobIDSearch());
            paramMap.put("createUser", contractUploadVO.getCreatedByUser());
            paramMap.put("createdDate", "SYSDATE");
            paramMap.put("region", contractUploadVO.getRegion());
            paramMap.put("updatedBy", contractUploadVO.getUpdatedByUser());
            paramMap.put("updatedDate", new java.sql.Date(contractUploadVO.getUpdatedDate().getTime())); 
            getNamedJdbcTemplate().update(contract_error_sql, paramMap);
        }

    } catch (Exception e) {
        Logger.getInstance().fatalEvent(getClass(),"insert", e.getMessage(), e); 
    }
}

If I pass "SYSDATE" instead of "new java.sql.Date(contractUploadVO.getUpdatedDate().getTime())" in updatedDate then its working fine.

Please help me why I am getting ORA-01858:non-numeric character was found error ??

Sushil
  • 364
  • 6
  • 22
  • 2
    How do you know that is the column with the error? – OldProgrammer May 31 '17 at 17:10
  • @OldProgrammer If I pass sysdate to "updatedDate" then its working fine. Thats how i got it. – Sushil May 31 '17 at 17:13
  • The string `'SYSDATE'` is not a valid date *value* for parameter `createdDate`. The value you add to `paramMap` is not an *expression*, so that is not the value of the Oracle `SYSDATE` expression. If you want to use `SYSDATE`, replace `:createdDate` with `SYSDATE` in the `contract_error_sql` string. – Andreas May 31 '17 at 17:20
  • @Andreas I dont have any problem if I use SYSDATE, its working fine. Problem is when I use java date object for that column. – Sushil May 31 '17 at 17:57
  • What driver are you using? Also, please show stacktrace. – Andreas May 31 '17 at 21:02
  • Possible duplicate of [Insert timestamp with JdbcTemplate in Oracle database ( ORA-01858 )](https://stackoverflow.com/questions/28198153/insert-timestamp-with-jdbctemplate-in-oracle-database-ora-01858) – Zico May 31 '17 at 22:23

0 Answers0