Oracle Instant Client and NLS_LANG in Windows environment
When using Oracle Instant client in a multi-lingual environment, the NLS_LANG parameter needs to be set manually. When installing other flavors of Oracle client, the Universal Installer does this for you automatically. The Instant client doesn't have any installer - it is just a bunch of libraries which you unzip into a directory of your choice. Our .NET application uses (also redistributes) the Instant client, so for its proper operation in multi-lingual environment it needs to correctly set NLS_LANG.
NLS_LANG is of the form <language>_<country/region>.<character set>
On Windows, NLS_LANG can be set in Registry or as an environmental variable. On UNIX it is usually set as an environmental variable. The current locale settings in effect determines the values for language, country/region and the character set. For example, when using a non-unicode console based client like SQLPlus On Windows 7, the current locale settings is determined by the Language for Non-Unicode programs under Control Panel -> Clock, Language and Region -> Region and Language ->Administrative . On my system this setting is English (United States) where English is the language and United States is the country/region . The corresponding terms in Oracle would be AMERICAN (language) and AMERICA (country/region). The character set is determined by the Windows codepage in use - which one can look up in the registry (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\) or use the National Language Support (NLS) API Reference. For English (United States) the ANSI codepage is 1252 and the OEM codepage is 437. The ANSI codepage is used for GUI programs and the OEM codepage is used for console programs. Since SQLPlus is a console program, the OEM codepage of 437 would be used. The corresponding Oracle character set for this OEM codepage would be US8PC437. The value of NLS_LANG in this case would be AMERICAN_AMERICA.US8PC437
If the client is a .NET application, the current locale can be found by looking at the CurrentCulture.Name property of System.Threading.Thread.CurrentThread. For example, on one of my test machines with a Chinese version of Windows 2008, the culture name is zh-CN, which in Oracle's terms translates to 'SIMPLIFIED CHINESE' (language) and 'CHINA' (country/region). And because .NET uses Unicode the character set in Oracle's terms would be UTF8 or AL32UTF8. The NLS_LANG would thenlook like
SIMPLIFIED CHINESE_CHINA.AL32UTF8
NLS_LANG is of the form <language>_<country/region>.<character set>
On Windows, NLS_LANG can be set in Registry or as an environmental variable. On UNIX it is usually set as an environmental variable. The current locale settings in effect determines the values for language, country/region and the character set. For example, when using a non-unicode console based client like SQLPlus On Windows 7, the current locale settings is determined by the Language for Non-Unicode programs under Control Panel -> Clock, Language and Region -> Region and Language ->Administrative . On my system this setting is English (United States) where English is the language and United States is the country/region . The corresponding terms in Oracle would be AMERICAN (language) and AMERICA (country/region). The character set is determined by the Windows codepage in use - which one can look up in the registry (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\) or use the National Language Support (NLS) API Reference. For English (United States) the ANSI codepage is 1252 and the OEM codepage is 437. The ANSI codepage is used for GUI programs and the OEM codepage is used for console programs. Since SQLPlus is a console program, the OEM codepage of 437 would be used. The corresponding Oracle character set for this OEM codepage would be US8PC437. The value of NLS_LANG in this case would be AMERICAN_AMERICA.US8PC437
If the client is a .NET application, the current locale can be found by looking at the CurrentCulture.Name property of System.Threading.Thread.CurrentThread. For example, on one of my test machines with a Chinese version of Windows 2008, the culture name is zh-CN, which in Oracle's terms translates to 'SIMPLIFIED CHINESE' (language) and 'CHINA' (country/region). And because .NET uses Unicode the character set in Oracle's terms would be UTF8 or AL32UTF8. The NLS_LANG would thenlook like
SIMPLIFIED CHINESE_CHINA.AL32UTF8
Its possible to leave the region/country portion blank, in which case a default matching the language would be chosen by Oracle. To help with mapping the language/region from Microsoft to Oracle here are two references
1. The National Language Support (NLS) API Reference lists all the supported locales on Windows.
2. Oracle's list of supported languages/regions can be found here in its Globalization Support Guide
Some more useful references on this topic - unfortunately they both require a support account
1. The correct NLS_LANG in a Windows Environment [ID 179133.1] (requires support account)
2. NLS_LANG Explained (How does Client-Server Character Conversion Work?) [ID 158577.1 (requires support account)
1. The National Language Support (NLS) API Reference lists all the supported locales on Windows.
2. Oracle's list of supported languages/regions can be found here in its Globalization Support Guide
Some more useful references on this topic - unfortunately they both require a support account
1. The correct NLS_LANG in a Windows Environment [ID 179133.1] (requires support account)
2. NLS_LANG Explained (How does Client-Server Character Conversion Work?) [ID 158577.1 (requires support account)