Setting NLS Parameters

Parameter Settings and Their Priorities

Highest Priority

• Explicitly set in SQL functions
  
• Set by an ALTER SESSION statement
  
• Set as an environment variable
  
• Specified in the initialization parameter file
  
• Default
  

Following Table lists the NLS parameters available with the Oracle server.

Parameter	Description	Default	Scope: I= INIT.ORA, E= Environment Variable, A= Alter Session
NLS_CALENDAR	Calendar system	Gregorian	I, E, A
NLS_COMP	SQL, PL/SQL operator comparison	Binary	I, E, A
NLS_CREDIT	Credit accounting symbol	NLS_TERRITORY	-, E, -
NLS_CURRENCY	Local currency symbol	NLS_TERRITORY	I, E, A
NLS_DATE_FORMAT	Date format	NLS_TERRITORY	I, E, A
NLS_DATE_LANGUAGE	Language for day and month names	NLS_LANGUAGE	I, E, A
NLS_DEBIT	Debit accounting symbol	NLS_TERRITORY	-, E, -
NLS_ISO_CURRENCY	ISO international currency symbol	NLS_TERRITORY	I, E, A
NLS_LANG	Language, territory, character set	AMERICAN_AMERICA. US7ASCII	-, E, -
NLS_LANGUAGE	Language	NLS_LANG	I, -, A
NLS_LENGTH_SEMANTICS	How strings are treated	Byte	I, -, A
NLS_LIST_SEPARATOR	Character separating items in a list	NLS_TERRITORY	-, E, -
NLS_MONETARY_CHARACTERS	Monetary symbol for dollar and cents (or their equivalents)	NLS_TERRITORY	-, E, -
NLS_NCHAR_CONV_EXCP	Reports data loss during a character type conversion		-, E, -
NLS_NUMERIC_CHARACTERS	Decimal character and group separator	NLS_TERRITORY	I, E, A
NLS_SORT	Character Sort Sequence	NLS_LANGUAGE	I, E, A
NLS_TERRITORY	Territory	NLS_LANG	I, -, A
NLS_TIMESTAMP_FORMAT	Timestamp	NLS_TERRITORY	I, E, A
NLS_TIMESTAMP_TZ_FORMAT	Timestamp with Timezone	NLS_TERRITORY	I, E, A
NLS_DUAL_CURRENCY	Dual currency symbol	NLS_TERRITORY	I, E, A

Choosing a Locale with the NLS_LANG Initialization Parameter

The NLS_LANG parameter has three components (language, territory, and character set) in the form:

NLS_LANG = language_territory.charset

Each component controls the operation of a subset of NLS features:

language Specifies conventions such as the language used for Oracle messages, sorting, day names, and month names. Each supported language has a unique name; for example, AMERICAN, FRENCH, or GERMAN. The language argument specifies default values for the territory and character set arguments. If language is not specified, the value defaults to AMERICAN.

territory Specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name; for example, AMERICA, FRANCE, or CANADA. If territory is not specified, the value defaults from the language value.

charset Specifies the character set used by the client application (normally that of the user's terminal). Each supported character set has a unique acronym, for example, US7ASCII, WE8ISO8859P1, WE8DEC, WE8EBCDIC500, or JA16EUC. Each language has a default character set associated with it.

The three arguments of NLS_LANG can be specified in many combinations, as in the following examples:

NLS_LANG = AMERICAN_AMERICA.US7ASCII

or

NLS_LANG = FRENCH_CANADA.WE8DEC

or

NLS_LANG = JAPANESE_JAPAN.JA16EUC

Note that illogical combinations can be set but will not work properly. For example, the following specification tries to support Japanese by using a Western European character set:

NLS_LANG = JAPANESE_JAPAN.WE8DEC

Because WE8DEC does not support any Japanese characters, you would be unable to store Japanese data.

Specifying NLS_LANG as an Environment Variable

. % setenv NLS_LANG FRENCH_FRANCE.WE8DEC

NLS_LANG Examples

The following examples show how date and number formats are affected by the NLS_LANG parameter. % setenv NLS_LANG American_America.WE8ISO8859P1

SQL> SELECT ename, hiredate, ROUND(sal/12,2)  sal FROM emp;
ENAME                         HIREDATE            SAL
-------                       ---------           -------
Clark                         09-DEC-88           4195.83
Miller                        23-MAR-92           4366.67
Strauß                        01-APR-95           3795.87

If NLS_LANG is set with the language as French, the territory as France, and the character set as Western European 8-bit ISO 8859-1, then the same query returns the following information:

% setenv NLS_LANG French_France.WE8ISO8859P1

SQL> SELECT ename, hiredate, ROUND(sal/12,2)  sal FROM emp;
ENAME                         HIREDATE           SAL
---------                     ---------          -------
Clark                         09/12/88           4195,83
Miller                        23/03/92           4366,67
Strauß                        01/04/95           3795,87

Overriding Language and Territory Specifications

The language and territory components of the NLS_LANG parameter set the default values for the other NLS parameters, such as date format, numeric characters, and linguistic sorting. Each of these detailed parameters can be set in the client environment to customize the language and territory values.

Note that NLS parameters in the client environment are ignored if NLS_LANG is not set.

If the NLS_LANG parameter is not set, the server session environment remains initialized with values of NLS_LANGUAGE, NLS_TERRRITORY, and other NLS instance parameters from the initialization parameter file. You can modify these parameters and restart the instance to change the defaults.

You might want to modify your NLS environment dynamically during the session. To do so, you can use NLS_LANGUAGE, NLS_TERRITORY and other NLS parameters in the ALTER SESSION statement.

The ALTER SESSION statement modifies only the session environment. The local client NLS environment is not modified, unless the client explicitly retrieves the new settings and modifies its local environment. SQL*Plus is an example of an application that retrieves new settings; Oracle Developer is an example of an application that does not retrieve new settings.

NLS Database Parameters