Setting NLS_NUMERIC_CHARACTERS Parameter
Establishing a Globalization Support Environment
6-18 Oracle Database 2 Day Developer's Guide
Using the NLS_NUMERIC_CHARACTERS Parameter
The
NLS_NUMERIC_CHARACTERS
parameter specifies the group separator and
decimal character. The group separator is the character that separates integer groups to
show thousands and millions, for example. The group separator is the character
returned by the
G
number format model. The decimal character separates the integer
and decimal parts of a number. Setting the
NLS_NUMERIC_CHARACTERS
parameter
overrides the default values derived from the setting of
NLS_TERRITORY
. The value
can be any two valid numeric characters for the group separator and decimal
character.
Any character can be the decimal character or group separator. The two characters
specified must be single-byte, and the characters must be different from each other.
The characters cannot be a numeric character or any of the following characters: plus
sign (+), minus sign (-), less than sign (<), greater than sign (>). Either character can be
a space.
To set the decimal character to a comma and the grouping separator to a period,
specify the
NLS_NUMERIC_CHARACTERS
parameter as follows:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";
SQL statements can include numbers represented as numeric or text literals. Numeric
literals are not enclosed in quotation marks. They are part of the SQL language syntax,
and always use a period as the decimal character and never contain a group separator.
Text literals are enclosed in single quotation marks. They are implicitly or explicitly
converted to numbers, if required, according to the current NLS settings.
To set the NLS_NUMERIC_CHARACTERS parameter:
The following example formats 4000 with the group separator and decimal character
specified in the
ALTER SESSION
statement.
1.
In SQL Developer, make a note of the current numeric characters format in which
Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database,
and then National Language Support Parameters. In the Select Connection dialog
box, select
hr_conn
from the list of connections. The current numeric characters
format is listed after
NLS_NUMERIC_CHARACTERS
.
2.
Set
NLS_NUMERIC_CHARACTERS
to the specified group separator and decimal
character.
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";
Use double quotation marks.
3.
Display 4000 using the format mask
'9G999D99'
.
SELECT TO_CHAR(4000, '9G999D99') FROM DUAL;
The output from the example should be similar to the following. The group
separator is the period (.) and the decimal character is the comma (,).
TO_CHAR(4000,'9G999D99')
------------------------
4.000,00
4.
Set
NLS_NUMERIC_CHARACTERS
back to its original setting listed in Step 1. For
example:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=". ";
