I can't understand the reason behind ORA-01722: invalid number

Question

I have a problem that is generated randomly (one time between thousandth of calls). The error" ORA-01722: invalid number" is generated when running the SQL update in a prepared statement in the oracle database. The case details are as following:

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 database all parameters are transferred perfectly through the JDBC driver except for the parameter 23410984 it was replaced by the value "<C4>^X*U" (this value has the carriage return before to the char 'u').

Answer 1

The fundamental reason is concerning java.sql.SQLException: ORA-01722: invalid number.

Perhaps the field last_case_status_history_id type is number, but your parameter is null.

Want to be a SQL expert? Come and join this SQL Certification course by Intellipaat.

If you want to know more about SQL, refer to the below SQL tutorial video that will help you out in a better way: