Geek asks and answers

I can not understand the reason ORA-01722: invalid number

I have a problem that is randomly generated (once between a thousandth of a conversation). Error ORA-01722: An invalid number is randomly generated while performing sql update in a prepared Oracle database. Details of the case are given 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:

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 tracking query parameters in the database, all parameters are transmitted correctly through the JDBC driver, with the exception of parameter 23410984 , it has been replaced by a value "<C4>^X* U"(note that this value contains a carriage return to char 'u'!). I do not know why

+5
source share
4 answers

- java.sql.SQLException: ORA-01722: invalid number.
  last_case_status_history_id type is number, null

+4

:

SELECT DUMP(23410984, 17)
FROM   dual;

:

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

, . 2 - NUMBER.

Oracle DUMP():

17 , , - ASCII EBCDIC. ASCII ^ X. . NLS .

, , NUMBER, .

+2

. java- hibernate " " aspectJ.

(Oracle 10G) - , ORA-01722. , 100%.

, , unboxing . , jdbc unboxing ( ). jdbc, 1.4 1.6.

, , :

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

, Statement :

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;
        }
    };
}

, .

, , , . ( , .) , , " " - - . ​​.

, , , 100%.

+2

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

I used UDF on top of the column where the number is expected, and I was getting a different value that is not a number. Thus, the operation does not trigger an invalid number exception.

0
source

More articles:


All Articles