Numbers can be stored in Oracle according to different formats. On one hand, we have the American way, whereby numbers are stored with a dot (.) as decimal indicator. On the other hand, we have a comma (,) as decimal indicator. Let me provide my own Oracle instance.
I have the following language settings:
select * from nls_session_parameters;
which provides:
NLS_LANGUAGE ENGLISH
NLS_TERRITORY AMERICA
NLS_NUMERIC_CHARACTERS .,
This means that numbers are interpreted with a dot (.) as decimal indicator. I created a small table with numbers that shows this dot:
It is possible to change the settings for a session. To that end, we fire next command:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ';
To check if numbers are now interpreted as having as decimal indicator being a comma, we try:
insert into nummer(getal) values('1,45');
This looks successful. To check if ‘1,45’ is really inserted, we display the data:
It is also possible to change the settings that determine the decimal point. These settings are derived from the computer one is working. On a windows machine, one may create a setting in the environmental variables:
A variable NLS_LANG either exists or must be created. On my machine, it is AMERICAN_AMERICA.WE8MSWIN1252.
Let us change this to DUTCH_THE NETHERLANDS.WE8MSWIN1252
It is also possible to set this variable in the registry:
This is done. Then the machine is restarted. After a restart, we check if the comma is used as decimal separator. This is the indeed the case:
