Number formats and JDBC voodoo
Ever had to insert some numeric values into an Oracle database? From your application through JDBC? You think “this is an everyday task – what should go wrong?” – well just read on…
Imagine you have got a simple table that has some numeric values that you want to update. Normally the easiest way to do this is to perform a simple update statement:
update distances set distance = 12.250 where id = 1;
And guess what happens? The row with the ID 1 is updated to the value 12.250.
Now we have got an application that has build its own OR-mapper. As it acts in a very generic way, it does not care which data is set on a certain field. So it treats numbers the same way as strings, which results in the following statement:
update distances set distance = '12.250' where id = 1;
No major problem one may think – oracle will just implicitly call the TO_NUMBER()
function and everything should work like a charm. Should – but does not. At least not always. Sometimes it fails with ORA-01722: invalid number
. But why?
Let’s take a close look at the TO_NUMBER()
function. The function can be configured through the (optional) nlsparams
, where one is NLS_NUMERIC_CHARACTERS=''dg''
. ‘d’ tells the function which character is used as the decimal separator, ‘g’ the group separator. If the conversion is done implicit it is not possible to set those parameters directly, instead those that are set through the environment variable NLS_NUMERIC_CHARACTERS
is used. If this variable is not set either, the oracle default value is used, which is ‘.,’ (UK format).
On our setup no value was set, so one might expect that the above statement should work. It did, as long as it was executed on the database server. If failed with ORA-01722
in case it was run from a remote database tool or on an application server. More strangely it succeeded if it was run in the following way (but only from remote, it failed if run from the server):
update distances set distance = '12,250' where id = 1;
So the only difference was the JDBC driver that is in-between. And there we have got the problem: The JDBC driver tries to do some “intelligent” conversion of your SQL statements. For that purpose it uses the system property user.language
. If this property is not set explicitly, is is set through the locale of the system it is running on. This is no problem as long as the application runs on a system that has a locale set to en_*. But as soon as it is run on a system that provides a locale with a different number format (like de_DE), the JDBC driver tries to convert the numeric values and the statement fails on the database.
So if your application connects your database through JDBC and you rely on the implicit TO_NUMBER()
conversion of Oracle, make sure the system property user.language
is set to the correct value!