Insert negative number - ORA-01722

Question

I'm using jdbcTemplate to insert into DataBase Oracle some data. Unfortunately I'm getting this error:

java.sql.SQLSyntaxErrorException: ORA-01722: invalid Number

First of all, I convert the string to double:

Number number = nmFormat.parse(record[8]);
double doubleVLBalance = number.doubleValue();

And After :

 String sqlSettlementBalance = "INSERT INTO STARR4.TBSETR_SETTLEMENT_BALANCE"+
                "(DT_MOVEMENT,NU_CUSTOMER_REF, NU_MOD_CUSTOMER_REF,"+
                "NU_HIERARCHICAL_NODE, CD_ACQUIRER, CD_PRODUCT,"+
                "CD_BALANCE_TYPE, NU_CUSTOMER, VL_BALANCE, NU_CONTROL)"+
                "VALUES ('" ...,'"+doubleVLBalance+"','"+sb.getNuControl()+"')";

jdbcTemplate.update(sqlSettlementBalance);

The value of the string record[8] is "-0.34". Any suggestion to solve this problem ?

score 3 · Accepted Answer · answered Jun 28 '17 at 19:08

If you surround a Number with quotes, it becomes a string. Remove the Quotes.

 String sqlSettlementBalance = "INSERT INTO STARR4.TBSETR_SETTLEMENT_BALANCE"+
            "(DT_MOVEMENT,NU_CUSTOMER_REF, NU_MOD_CUSTOMER_REF,"+
            "NU_HIERARCHICAL_NODE, CD_ACQUIRER, CD_PRODUCT,"+
            "CD_BALANCE_TYPE, NU_CUSTOMER, VL_BALANCE, NU_CONTROL)"+
            "VALUES ('" ...,"+doubleVLBalance+",'"+sb.getNuControl()+"')";

score 3 · Answer 2 · answered Jun 28 '17 at 19:21

As mention before you should delete quotes, but I recommend instead of

jdbcTemplate.update(sqlSettlementBalance);

using

jdbcTemplate.update(sqlSettlementBalance, Object... args);

for example something like this:

 String sqlSettlementBalance = "INSERT INTO STARR4.TBSETR_SETTLEMENT_BALANCE"+
            "(DT_MOVEMENT,NU_CUSTOMER_REF, NU_MOD_CUSTOMER_REF,"+
            "NU_HIERARCHICAL_NODE, CD_ACQUIRER, CD_PRODUCT,"+
            "CD_BALANCE_TYPE, NU_CUSTOMER, VL_BALANCE, NU_CONTROL)"+
            "VALUES ('"...,?,'"+sb.getNuControl()+"')";

jdbcTemplate.update(sqlSettlementBalance, doubleVLBalance);

Of use PreparedStatement see example this

Insert negative number - ORA-01722

2 Answers2