ORA-01722: invalid number

Question

When i execute the below SQL command using single quotes to enter a number, i got an error if remove the single quotes,it is successfully updated. knowing that the type of the field HEIGHT is NUMBER.

The strange thing is that i tried to use the same sql statement with single quotes on different machines, some machines execute it successfully, others do not.(same oracle version,same table structure...)

Any explanation please

SQL> UPDATE TBL_DEVICE_INFO SET HEIGHT='14.5' WHERE ID='6ujbfI'; UPDATE TBL_DEVICE_INFO SET HEIGHT='14.5' WHERE ID='6ujbfI' * ERREUR à la ligne 1 : ORA-01722: invalid number

SQL> UPDATE TBL_DEVICE_INFO SET HEIGHT=14.5 WHERE ID='6ujbfI';

1 row updated.

Answer 1

Strings should be quoted using single quotes, numbers shouldn't be.

Maybe you're using a different client on the machines where the invalid syntax works?

Answer 2

This is most likely a locale problem.

That is, some machines have the decimal symbol "." (period), and some have "," (comma).

You can test it by putting it like this:

UPDATE TBL_DEVICE_INFO 
   SET HEIGHT = to_number('14.5', '99D9','NLS_NUMERIC_CHARACTERS = ''. ''') 
   WHERE ID='6ujbfI'

When the number is in single qoutes, oracle will do an implicit conversion to number using the characters set in the database.

You can change the default by setting the NLS_NUMERIC_CHARACTERS parameter:

alter session set NLS_NUMERIC_CHARACTERS = '. ';

but that will also reflect to data returned by the system so make sure that it doesn't break anything in your application if you change that.