详解Oracle中NLS_LANG变量的使用

185次阅读

共计 5557 个字符，预计需要花费 14 分钟才能阅读完成。

Oracle provides Globalization Support that enables users to interact with a database in their own language, as defined by the NLS_LANG parameter. When you install Oracle Database Client components, Oracle Universal Installer sets the NLS_LANG parameter in the registry.

The locale setting of your operating system determines the value of the NLS_LANG parameter at installation. Table C-1 lists the operating system locale and NLS_LANG value mappings.

The NLS_LANG parameter is stored in the registry under the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\NLS_LANG subkey, where ID is the unique number identifying the Oracle home.

The NLS_LANG parameter uses the following format:

NLS_LANG = LANGUAGE_TERRITORY.CHARACTER_SET

Oracle 提供全球化的支持，通过定义 NLS_LANG 参数能够让用户用他们自己的语言使用数据库。当安装 Oracle 数据库客户端的时候，安装程序通常会在注册表中设置 NLS_LANG 参数。

在安装中，操作系统的区域设置会决定 NLS_LANG 参数的值。表 C - 1 列出了操作系统区域和 NLS_LANG 的对照表。

NLS_LANG 参数被存储在注册表中的 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\NLS_LANG 子项下，其中 ID 是识别 Oracle home 的唯一数字。
NLS_LANG 参数使用如下的格式：
NLS_LANG = LANGUAGE_TERRITORY.CHARACTER_SET

where:

Parameter	Description
`LANGUAGE`	Specifies the language and conventions for displaying messages, day name, and month name.
`TERRITORY`	Specifies the territory and conventions for calculating week and day numbers.
`CHARACTER_SET`	Controls the character set used for displaying messages.

2 NSL_LANG 常用的值

Table C-1 lists commonly used NLS_LANG values for various operating system locales:

Table C-1 NLS_LANG Parameter Values

Operating System Locale	NLS_LANG Value
Arabic (U.A.E.)	`ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256`
Bulgarian	`BULGARIAN_BULGARIA.CL8MSWIN1251`
Catalan	`CATALAN_CATALONIA.WE8MSWIN1252`
Chinese (PRC)	`SIMPLIFIED CHINESE_CHINA.ZHS16GBK`
Chinese (Taiwan)	`TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950`
Croatian	`CROATIAN_CROATIA.EE8MSWIN1250`
Czech	`CZECH_CZECH REPUBLIC.EE8MSWIN1250`
Danish	`DANISH_DENMARK.WE8MSWIN1252`
Dutch (Netherlands)	`DUTCH_THE NETHERLANDS.WE8MSWIN1252`
English (United Kingdom)	`ENGLISH_UNITED KINGDOM.WE8MSWIN1252`
English (United States)	`AMERICAN_AMERICA.WE8MSWIN1252`
Estonian	`ESTONIAN_ESTONIA.BLT8MSWIN1257`
Finnish	`FINNISH_FINLAND.WE8MSWIN1252`
French (Canada)	`CANADIAN FRENCH_CANADA.WE8MSWIN1252`
French (France)	`FRENCH_FRANCE.WE8MSWIN1252`
German (Germany)	`GERMAN_GERMANY.WE8MSWIN1252`
Greek	`GREEK_GREECE.EL8MSWIN1253`
Hebrew	`HEBREW_ISRAEL.IW8MSWIN1255`
Hungarian	`HUNGARIAN_HUNGARY.EE8MSWIN1250`
Icelandic	`ICELANDIC_ICELAND.WE8MSWIN1252`
Indonesian	`INDONESIAN_INDONESIA.WE8MSWIN1252`
Italian (Italy)	`ITALIAN_ITALY.WE8MSWIN1252`
Japanese	`JAPANESE_JAPAN.JA16SJIS`
Korean	`KOREAN_KOREA.KO16MSWIN949`
Latvian	`LATVIAN_LATVIA.BLT8MSWIN1257`
Lithuanian	`LITHUANIAN_LITHUANIA.BLT8MSWIN1257`
Norwegian	`NORWEGIAN_NORWAY.WE8MSWIN1252`
Polish	`POLISH_POLAND.EE8MSWIN1250`
Portuguese (Brazil)	`BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252`
Portuguese (Portugal)	`PORTUGUESE_PORTUGAL.WE8MSWIN1252`
Romanian	`ROMANIAN_ROMANIA.EE8MSWIN1250`
Russian	`RUSSIAN_CIS.CL8MSWIN1251`
Slovak	`SLOVAK_SLOVAKIA.EE8MSWIN1250`
Spanish (Spain)	`SPANISH_SPAIN.WE8MSWIN1252`
Swedish	`SWEDISH_SWEDEN.WE8MSWIN1252`
Thai	`THAI_THAILAND.TH8TISASCII`
Spanish (Mexico)	`MEXICAN SPANISH_MEXICO.WE8MSWIN1252`
Spanish (Venezuela)	`LATIN AMERICAN SPANISH_VENEZUELA.WE8MSWIN1252`
Turkish	`TURKISH_TURKEY.TR8MSWIN1254`
Ukrainian	`UKRAINIAN_UKRAINE.CL8MSWIN1251`
Vietnamese	`VIETNAMESE_VIETNAM.VN8MSWIN1258`

3 在 MS-DOS 模式和 Batch 模式中设置 NLS_LANG

Before you can use Oracle utilities such as SQL*Plus, SQL Loader, Import, and Export in MS-DOS mode, make sure that you have set the character set field of the NLS_LANG parameter for the session to the correct value.

This is required because MS-DOS mode uses, with a few exceptions, a different character set (or code-page) from Windows (ANSI code-page), and the default Oracle home NLS_LANG parameter in the registry is always set to the appropriate Windows code-page. If you do not set the NLS_LANG parameter for the MS-DOS mode session correctly, incorrect character conversion can corrupt error messages and data.

For Japanese, Korean, Simplified Chinese, and Traditional Chinese, the MS-DOS code-page is identical to the ANSI code-page. In this case, you do not need to set the NLS_LANG parameter in MS-DOS mode.

Similarly, in batch mode, set the correct character set value of NLS_LANG by inserting a SET NLS_LANG command at the start of the batch procedure, according to the character set of the files to be processed in the procedure.

在你使用 Oracle 应用之前，比如在 MS-DOS 模式中使用 SQL*Plus,SQL Loader,Import, 和 Export，确保你已经在本次会话中正确的设置了字符集属性和 NLS_LANG 参数属性。

这是必须的，因为 MS-DOS 模式的使用会带有一些的来着 Windows(ANSI code-page) 的不同字符集设置的异常，在注册表中默认的 Oracle home NLS_LANG 参数总是会被设置成适合 Windows code-page 的值。

如果你在 MS-DOS 模式中不正确的设置 NLS_LANG 参数的值，那么错误的字符转换将会损坏信息和数据。

对于日文，韩文，简体中文和繁体中文，MS—DOS code-page 和 ANSI code-page 的值一样。在这种情况下，你不需要在 MS-DOS 模式中设置 NLS_LANG 参数的值。

同样的，在 Batch 模式中，根据程序运行过程中被处理的文件字符，通过插入 SET LANG_LANG 命令设置正确的 NLS_LANG 值。

Table C-2 lists the Oracle character sets that correspond to the MS-DOS mode for various operating system locales:

Table C-2 Oracle Character Sets for Operating System Locales

Operating System Locale	Character Set
Catalan	`WE8PC850`
Chinese (PRC)	`ZHS16GBK`
Chinese (Taiwan)	`ZHT16MSWIN950`
Czech	`EE8PC852`
Danish	`WE8PC850`
Dutch	`WE8PC850`
English (United Kingdom)	`WE8PC850`
English (United States)	`US8PC437`
Finnish	`WE8PC850`
French	`WE8PC850`
German	`WE8PC850`
Greek	`EL8PC737`
Hungarian	`EE8PC852`
Italian	`WE8PC850`
Japanese	`JA16SJIS`
Korean	`KO16MSWIN949`
Norwegian	`WE8PC850`
Polish	`EE8PC852`
Portuguese	`WE8PC850`
Romanian	`EE8PC852`
Russian	`RU8PC866`
Slovak	`EE8PC852`
Slovenian	`EE8PC852`
Spanish	`WE8PC850`
Swedish	`WE8PC850`
Turkish	`TR8PC857`
Catalan	`WE8PC850`

4，注册表中 NLS_LANG 和系统环境变量中的 NLS_LANG

当用户安装好 Oracle 客户端后会在注册表中出现 NLS_LANG 的注册信息，在操作系统的环境变量中用户也可以设置自己的 NLS_LANG 的值。

但是需要注意：

注册表中的 NLS_LANG 是用于对应 Oracle 数据库与 windows 的 code-page 值的，这个值是在安装的过程中自动添加的，一般不要去修改，如果修改后 NLS_LANG 的值与 code-page 的值不对应的话，会出现不同字符的异常。

系统环境变脸中 NLS_LANG 是用于对应 Oracle 数据显示的，这个值在安装完成后可能会有，也可能没有，是可以修改的，如果希望以中文显示则修改为 NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK，也可以是英文的 NLS_LANG=AMERICAN_AMERICA.UTF8，建议在使用 Oracle 数据库之间将这里的 NLS_LANG 设置为英文显示。