Oracle numerical data

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: