I can't understand the reason behind ORA-01722: invalid number
java, jdbc, ora-01722, oracle, prepared-statement
Solution
The key reason is about `java.sql.SQLException: ORA-01722: invalid number`. May be the field `last_case_status_history_id` type is number, but your parameter is null
Problem
I have an issue that is generated randomly (one time between thousandth of calls). The error ORA-01722: invalid number is generated in a random way while executing sql update in a prepared statement Oracle database. The case details are as below: ``` try { connection = getConnection(); statement = connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { if (params[i] instanceof Date) { statement.setTimestamp(i + 1, new Timestamp(((Date) params[i]).getTime())); } else if (params[i] instanceof java.util.Date) { statement.setTimestamp(i + 1, new Timestamp(((java.util.Date) params[i]).getTime())); } else { statement.setObject(i + 1, params[i]); } paramsBuilder.append(": " + params[i]); } if (logger.isInfoEnabled()) { logger.info("Query String [" + sql + "] [" + paramsBuilder + "]"); logger.info("Query Parameters [" + paramsBuilder + "]"); } result = statement.executeUpdate(); if (logger.isInfoEnabled()) { logger.info(result + " rows affected"); } } catch (SQLException e) { if (logger.isInfoEnabled()) { String message = "Failed to execute SQL statment [" + sql + "] with parameters [" + paramsBuilder + "]"; logger.error(message, e); } throw new DAOException(e); } ``` and the value in log is like that : ``` Failed to execute SQL statment [update CUSTOMER_CASE set no_of_ptp=?, no_of_unreached=?,collector_name=? , last_case_status_history_id=?, current_handler=?, handling_start_time=?,due_total_open_amount=?, payment_due_invoice_id =? where id=?] with parameters [: 0: 0: auto: 5470508: null: null: 0.0: 23410984: 2476739] java.sql.SQLException: ORA-01722: invalid number ``` by tracing the query parameters at DB all parameters are transferred correctly through JDBC driver except for the parameter 23410984 it was replaced by the value `"<C4>^X* U"` (note this value contains carriage return before char 'u' !). I don't know why