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

Question

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

Answer 1

I tried this:

SELECT DUMP(23410984, 17)
FROM   dual;

and got this:

Typ=2 Len=5: c4,^X,*,^J,U

which is practically the same as what you got. Type 2 is NUMBER data type.

Oracle documentation says for the second parameter of the DUMP() function:

17 returns each byte printed as a character if and only if it can be interpreted as a printable character in the character set of the compiler—typically ASCII or EBCDIC. Some ASCII control characters may be printed in the form ^X as well. Otherwise the character is printed in hexidecimal notation. All NLS parameters are ignored.

So, it seems that sometimes the value is transferred not as the internal byte format of NUMBER but as string.

Answer 2

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

Answer 3

I had the same problem in case of jdbc, when I was trying to execute insert query. The reason was the order of columns in sql plus(Oracle) was different in comparison to the prepared statement commands I was executing. We have to specify the correct parameter corresponding to sql plus columns.

Sql plus column format:

SQL> select * from employee;

   EID EDATE      EJOB                 ENAME                      ESAL

---

Jdbc Code:

public static int addEmployee(Emp e) throws SQLException, ClassNotFoundException {
    con=getCon();
    String sql="insert into employee values(?,?,?,?,?)";
    ps=con.prepareStatement(sql);
    ***ps.setInt(1,e.getEmployeeId() );       //1 means: sql plus column eid
    ps.setString(2, e.getEmployeeHireDate()); //2 means: sql plus column: edate
    ps.setString(3, e.getEmployeeJob());      //3 means: sql plus column: ejob
    ps.setString(4, e.getEmployeeName());     //4 means: sql plus column: ename
    ps.setInt(5, e.getEmployeeSalary());***   //5 means: sql plus column: esal
    int i=ps.executeUpdate();
    con.close();
    return i;
}

Answer 4

We had a similar issue. Our hibernate based java code issued a prepared statement to fill in user info and a 'reason for change' for all save operations by using aspectJ.

In 3 separate database environments (Oracle 10G) this worked without any issues, but on the production database this sometimes failed with the ORA-01722 error. This only happens when the cpu load on the database server is close to 100%.

On another forum I found a suggestion about not passing along Long objects but doing the unboxing explicitly to a long primitive. It seems the jdbc driver or the database itself has issues when doing this unboxing on high load (even if that sounds pretty crazy). Various jdbc drivers were tested, such as the 1.4 and 1.6 versions.

The code that sometimes failed was:

private void execute(final Long userId, final String rfc) {
    Object[] args = new Object[]{ userId, rfc };
    getJdbcTemplate().update("call schema.package.setUserAndRFC(?,?)", args);
}

Now we changed the code to make an explicit preparedStatement like this:

private void execute(final Long userId, final String rfc) {
    getJdbcTemplate().update(prepareStatement(userId.longValue(), rfc));
}

private PreparedStatementCreator prepareStatement(final long userId, final String rfc) {
    return new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement statement = con.prepareStatement("call schema.package.setUserAndRFC(?,?) ");
            statement.setLong(1, userId);
            statement.setString(2, rfc);
            return statement;
        }
    };
}

This issue has not happened since, even while the environment stayed the same with the same software and high load on the database.

I've heard from a colleague that one of the DBA's could see in the logging that the prepared statement was accepted by the database, but no cpu was assigned to it. (Makes sense since all cpu's would be busy on such a high load.) It might be that simply the wrong error is thrown, a 'database overload' error of some sorts should be thrown or something. Even better would be to never create such an error anyway.

I guess it's primarily the database load though, it's not smart at all to let the cpu load reach 100% all the time.

Answer 5

Some times it happens when we are using IN clause and passing the values as IN ('1, 2, 3, 4') instead of passing it in following way IN ('1','2','3','4')

Answer 6

java.sql.SQLException: ORA-01722: invalid number.

I was using a UDF on top of the column where a number is expected and i was getting a different value which is not a number. So the operation fails throwing an invalid number exception.