3 Setting Up a Globalization Support Environment
This chapter tells how to set up a globalization support environment. It includes the following topics:
- Setting NLS Parameters
- Choosing a Locale with the NLS_LANG Environment Variable
- Character Set Parameter
- NLS Database Parameters
- Language and Territory Parameters
- Date and Time Parameters
- Calendar Definitions
- Numeric and List Parameters
- Monetary Parameters
- Linguistic Sort Parameters
- Character Set Conversion Parameter
- Length Semantics
Setting NLS Parameters
NLS (National Language Support) parameter s determine the locale-specific behavior on both the client and the server. NLS parameters can be specified in the following ways:
- As initialization parameters on the server You can include parameters in the initialization parameter file to specify a default session NLS environment. These settings have no effect on the client side; they control only the server’s behavior. For example:
NLS_TERRITORY = "CZECH REPUBLIC"
ALTER SESSION SET NLS_SORT = FRENCH;
See Also : Oracle Database SQL Language Reference for more information about the ALTER SESSION statement
TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')
See Also: Oracle Database SQL Language Reference for more information about SQL functions, including the TO_CHAR function
Table 3-1 shows the precedence order of the different methods of setting NLS parameters. Higher priority settings override lower priority settings. For example, a default value has the lowest priority and can be overridden by any other method.
Table 3-1 Methods of Setting NLS Parameters and Their Priorities
Explicitly set in SQL functions
Set by an ALTER SESSION statement
Set as an environment variable
Specified in the initialization parameter file
Table 3-2 lists the available NLS parameters. Because the SQL function NLS parameters can be specified only with specific functions, the table does not show the SQL function scope.
SQL, PL/SQL operator comparison
Derived from NLS_TERRITORY
Derived from NLS_TERRITORY
Derived from NLS_TERRITORY
Language for day and month names
Derived from NLS_LANGUAGE
Derived from NLS_TERRITORY
ISO international currency symbol
Derived from NLS_TERRITORY
Language, territory, character set
Character that separates items in a list
Derived from NLS_TERRITORY
Monetary symbol for dollar and cents (or their equivalents)
Derived from NLS_TERRITORY
Reports data loss during a character type conversion
Decimal character and group separator
Derived from NLS_TERRITORY
Derived from NLS_LANGUAGE
Derived from NLS_TERRITORY
Derived from NLS_TERRITORY
Derived from NLS_TERRITORY
Choosing a Locale with the NLS_LANG Environment Variable
A locale is a li nguistic and cultural environment in which a system or program is running. Setting the NLS_LANG environment parameter is the simplest way to specify locale behavior for Oracle Database software. It sets the language and territory used by the client application and the database server. It also sets the client’s character set, which is the character set for data entered or displayed by a client program.
NLS_LANG is set as an environment variable on UNIX platforms. NLS_LANG is set in the registry on Windows platforms.
The NLS_LANG parameter has three components: language, territory, and character set. Specify it in the following format, including the punctuation:
NLS_LANG = language_territory.charset For example, if the Oracle Universal Installer does not populate NLS_LANG , then its value by default is AMERICAN_AMERICA.US7ASCII . The language is AMERICAN , the territory is AMERICA , and the character set is US7ASCII . The values in NLS_LANG and other NLS parameters are case-insensitive.
Each component of the NLS_LANG parameter controls the operation of a subset of globalization support features:
- language Specifies conventions such as the language used for Oracle Database 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 the language is not specified, then 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 the territory is not specified, then the value is derived from the language value.
- charset Specifies the character set used by the client application (normally the Oracle Database character set that corresponds to the user’s terminal character set or the OS character set). Each supported character set has a unique acronym, for example, US7ASCII , WE8ISO8859P1 , WE8DEC , WE8MSWIN1252 , or JA16EUC . Each language has a default character set associated with it.
Note : All components of the NLS_LANG definition are optional; any item that is not specified uses its default value. If you specify territory or character set, then you must include the preceding delimiter [underscore (_) for territory, period (.) for character set]. Otherwise, the value is parsed as a language name. For example, to set only the territory portion of NLS_LANG , use the following format: NLS_LANG=_JAPAN
The three components of NLS_LANG can be specified in many combinations, as in the following examples:
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252 NLS_LANG = FRENCH_CANADA.WE8ISO8859P1 NLS_LANG = JAPANESE_JAPAN.JA16EUC
Note that illogical combinations can be set but do not work properly. For example, the following specification tries to support Japanese by using a Western European character set:
NLS_LANG = JAPANESE_JAPAN.WE8ISO8859P1
Because the WE8ISO8859P1 character set does not support any Japanese characters, you cannot store or display Japanese data if you use this definition for NLS_LANG .
The rest of this section includes the following topics:
- Appendix A, «Locale Data» for a complete list of supported languages, territories, and character sets
- Your operating system documentation for information about additional globalization settings that may be necessary for your platform
Specifying the Value of NLS_LANG
In a UNIX operating system C-shell session, you can specify the value of NLS_LANG by entering a statement similar to the following example:
% setenv NLS_LANG FRENCH_FRANCE.WE8ISO8859P1
Because NLS_LANG is an environment variable, it is read by the client application at startup time. The client communicates the information defined by NLS_LANG to the server when it connects to the database server.
The following examples show how date and number formats are affected by the NLS_LANG parameter.
Example 3-1 Setting NLS_LANG to American_America.WE8ISO8859P1
Set NLS_LANG so that the language is AMERICAN , the territory is AMERICA , and the Oracle Database character set is WE8ISO8859P1 :
% setenv NLS_LANG American_America.WE8ISO8859P1
SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;
You should see results similar to the following output:
LAST_NAME HIRE_DATE SALARY ------------------------- --------- ---------- . Sciarra 30-SEP-05 962.5 Urman 07-MAR-06 975 Popp 07-DEC-07 862.5 .
Example 3-2 Setting NLS_LANG to French_France.WE8ISO8859P1
Set NLS_LANG so that the language is FRENCH , the territory is FRANCE , and the Oracle Database character set is WE8ISO8859P1 :
% setenv NLS_LANG French_France.WE8ISO8859P1
Then the query shown in Example 3-1 returns the following output:
LAST_NAME HIRE_DATE SALARY ------------------------- --------- --------- . Sciarra 30/09/05 962,5 Urman 07/03/06 975 Popp 07/12/07 862,5 .
Note that the date format and the number format have changed. The numbers have not changed, because the underlying data is the same.
Overriding Language and Territory Specifications
The NLS_LANG parameter sets the language and territory environment used by both the server session (for example, SQL command execution) and the client application (for example, display formatting in Oracle Database tools). Using this parameter ensures that the language environments of both the database and the client application are automatically the same.
The language and territory components of the NLS_LANG parameter determine the default values for other detailed NLS parameters, such as date format, numeric characters, and linguistic sorting. Each of these detailed parameters can be set in the client environment to override the default values if the NLS_LANG parameter has already been set.
If the NLS_LANG parameter is not set, then the server session environment remains initialized with values of NLS_LANGUAGE , NLS_TERRITORY , 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 the NLS environment dynamically during the session. To do so, you can use the ALTER SESSION statement to change NLS_LANGUAGE , NLS_TERRITORY , and other NLS parameters.
You cannot modify the setting for the client character set with 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.
Locale Variants
Before Oracle Database 10 g , Oracle defined language and territory definitions separately. This resulted in the definition of a territory being independent of the language setting of the user. Since Oracle Database 10 g , some territories can have different date, time, number, and monetary formats based on the language setting of a user. This type of language-dependent territory definition is called a locale variant.
For the variant to work properly, both NLS_TERRITORY and NLS_LANGUAGE must be specified.
Table 3-3 shows the territories that have been enhanced to support variations.
Table 3-3 Oracle Database Locale Variants
Oracle linux nls lang
The NLS_LANG environment variable determines the language of the user interface and the globalization behavior for components such as SQL*Plus, exp, and imp.
It sets the language and territory used by the client application and the database user session. It also declares the character set for entering and displaying data by the client application.
The NLS_LANG environment variable uses the following format:
NLS_LANG=language_territory.characterset
- language specifies the language used for displaying Oracle messages, sorting, day names, and month names
- territory specifies the conventions for default date, monetary and numeric formats
- characterset specifies the encoding used by the client application In most cases, this is the Oracle character set that corresponds to the Windows ANSI Code Page as determined by the System Locale.
The NLS_LANG parameter on Windows can be set
- in Registry under the subkey corresponding to a given Oracle home,
- as an environment variable.
When you install Oracle Database components and the NLS_LANG parameter is not yet set in the Registry subkey of the target Oracle home, Oracle Universal Installer sets the NLS_LANG parameter to a default value derived from the operating system locale for the current user. See the following table.
- Arabic (U.A.E.) — ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256
- Chinese (PRC) — SIMPLIFIED CHINESE_CHINA.ZHS16GBK
- Chinese (Taiwan) — TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
- English (United Kingdom) — ENGLISH_UNITED KINGDOM.WE8MSWIN1252
- English (United States) — AMERICAN_AMERICA.WE8MSWIN1252
- French (Canada) — CANADIAN FRENCH_CANADA.WE8MSWIN1252
- French (France) — FRENCH_FRANCE.WE8MSWIN1252
- German (Germany) — GERMAN_GERMANY.WE8MSWIN1252
- Hebrew — HEBREW_ISRAEL.IW8MSWIN1255
- Japanese — JAPANESE_JAPAN.JA16SJISTILDE
- Russian — RUSSIAN_RUSSIA.CL8MSWIN1251
- Spanish (Spain) — SPANISH_SPAIN.WE8MSWIN1252
- Spanish (Mexico) — MEXICAN SPANISH_MEXICO.WE8MSWIN1252
- Spanish (Venezuela) — LATIN AMERICAN SPANISH_VENEZUELA.WE8MSWIN1252
Oracle Database Globalization Support Guide for information about the NLS_LANG parameter and Globalization Support initialization parameters