NLS_LANG FAQ

As an FAQ you can easily navigate to the sections of interest. It is recommended to start with the NLS_LANG Parameter Fundamentals section first to get a basic understanding of how the NLS_LANG parameter works.

NLS_LANG Parameter Fundamentals

Common NLS_LANG Myths

Checking the current NLS_LANG Setting

The Priority of NLS Parameters related to NLS_LANG

Session Parameters

Instance Parameters

Database Parameters

An example of a wrong NLS_LANG setup

How to setup the NLS_LANG Properly for UNIX

How to setup the NLS_LANG Properly for Windows and DOS Code Pages

Where to set the NLS_LANG in Windows

Determine your Windows ANSI code page

The correct NLS_LANG for Windows Command Line Operations

List of common NLS_LANG settings used in the Windows Registry:

List of common NLS_LANG settings used in the Command Prompt (DOS box)

Other Frequently asked questions regarding NLS_LANG

What does the LANGUAGE component of the NLS_LANG parameter control?

What does the TERRITORY component of the NLS_LANG parameter control?

How to see what's really stored in the database?

Where is the Character Conversion Done?

Windows SQL*Plus is not showing all my extended characters?

I get a question mark or inverted question mark when selecting back just inserted characters?

Is iSQL*Plus the only unicode enabled client we support?

What about command line tools like SQL*Loader, Import, Export, utilities?

What about database links?

What about Multiple Homes on Windows?

Is there an Oracle Unicode Client on Windows?

What is a Character set or Code Page?

Why Are There Different Character sets?

What is the difference between 7 bit, 8 bit and Unicode Character sets?

How to choose the right database character set?

NLS_LANG Parameter Fundamentals

A locale is a set of information addressing linguistic and cultural requirements that corresponds to a given language and country. Traditionally, the data associated with a locale provides support for formatting and parsing of dates, times, numbers, and currencies, etc. Providing current and correct locale data has historically been the responsibility of each platform owner or vendor, leading to inconsistencies and errors in locale data.

Setting the NLS_LANG environment parameter is the simplest way to specify locale behavior for Oracle software. It sets the language and territory used by the client application and the database server. It also indicates the client's character set, which corresponds to the character set for data to be entered or displayed by a client program.

NLS_LANG is set as a local 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

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 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 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.

The remainder of this document will focus on the charset component of the NLS_LANG setting, as it is the least understood and most important piece to set correctly.

Top of the Document

Common NLS_LANG Myths

·         Setting the NLS_LANG to the character set of the database MAY be correct but IS often not correct. DO NOT assume that NLS_LANG needs to be the same as the database character set. THIS IS OFTEN NOT TRUE.

·         The character set defined with the NLS_LANG parameter does NOT CHANGE your client's character set. It is used to let Oracle know what character set you are USING on the client side, so Oracle can do the proper conversion. You cannot change the character set of your client by using a different NLS_LANG!

·         If you don't set the NLS_LANG on the client it uses the NLS_LANG of the server. This is also NOT true! For example, if the Oracle Installer does not populate NLS_LANG, and it is not otherwise set then its value by default is AMERICAN_AMERICA.US7ASCII. The language is AMERICAN, the territory is AMERICA, and the character set is US7ASCII.

·         Setting the LANGUAGE and TERRITORY parameters of NLS_LANG has nothing to do with the ability to store characters in a database. A NLS_LANG set to JAPANESE_JAPAN.WE8MSWIN1252 will not allow you to store Japanese, as WE8MSWIN1252 doesn't support Japanese characters. However a NLS_LANG set to AMERICAN_AMERICA.JA16SJIS will allow you to store Japanese providing the input data is truly JA16SJIS and if the database is also in a character set that can store Japanese like UTF8 or JA16SJIS)

Top of the Document

Checking the current NLS_LANG Setting

In many cases the NLS_LANG has been already set during the Oracle install or thereafter manually. To be sure you can use these methods to get back the value of NLS_LANG for SQL*Plus:

On UNIX:

SQL> HOST ECHO $NLS_LANG

This returns the value of the parameter.

On Windows:

On Windows you have two possible options, normally the NLS_LANG is set in the registry, but it can also be set in the environment, however this is not often done. The value in the environment takes precedence over the value in the registry and is used for ALL Oracle_Homes on the server. Also note that any USER environment variable takes precedence over any SYSTEM environment variable (this is Windows behavior, and has nothing to do with Oracle) if set.

To check if it's set in the environment:

 SQL> HOST ECHO %NLS_LANG% 

If this reports just %NLS_LANG% back, the variable is not set in the environment.

If it's set it reports something like

 ENGLISH_UNITED KINGDOM.WE8ISO8859P1 

If NLS_LANG is not set in the environment, check the value in the registry:

SQL>@.[%NLS_LANG%].

If you get something like:

 Unable to open file.[ENGLISH_UNITED KINGDOM.WE8ISO8859P1].

The "file name" between the braces is the value of the registry parameter.

If you get this as result:

Unable to open file ".[%NLS_LANG%]." then the parameter NLS_LANG is also not set in the registry.

Note the @.[%NLS_LANG%]. technique reports the NLS_LANG known by the SQL*Plus executable, it will not read the registry itself. But if you run the HOST command first and the NLS_LANG is not set in the environment then you can be sure the variable is set in the registry if the @.[%NLS_LANG%]. returns a valid value.

All other NLS parameters can be retrieved by a:

SELECT * FROM NLS_SESSION_PARAMETERS; 

Top of the Document

The Priority of NLS Parameters related to NLS_LANG

This section explains the order in which NLS parameters are taken into account in the database client/server model. (This does NOT cover Thin JDBC connections)

There are 3 levels at which you can set NLS parameters: Database, Instance and

Session. If a parameter is defined at more than one level then the rules on which one takes precedence are quite straightforward:

1. NLS database settings are superseded by NLS instance settings

2. NLS database & NLS instance settings are superseded by NLS session settings

Session Parameters

SELECT * from NLS_SESSION_PARAMETERS;

These are the settings used for the current SQL session.

These reflect (in this order):

1) The values of NLS parameters set by "ALTER SESSION "

ALTER SESSION set NLS_DATE_FORMAT = 'DD/MM/YYYY';

2) If there is no explicit "ALTER SESSION " statement done then it reflects the setting of the corresponding NLS parameter on the client derived from the NLS_LANG variable.

3) If NLS_LANG is specified with only the <Territory> part then AMERICAN is used as default <Language>.

So if you set NLS_LANG=_BELGIUM. WE8MSWIN1252 then you get this:

PARAMETER VALUE

------------------------------ --------------

NLS_LANGUAGE AMERICAN

NLS_TERRITORY BELGIUM

NLS_CURRENCY <euro sign here>

NLS_ISO_CURRENCY BELGIUM

....

4) If NLS_LANG is specified with only the <Language> part then the <Territory> defaults to a setting based on <Language>.

So if you set NLS_LANG=ITALIAN_.WE8MSWIN1252 then you get this:

PARAMETER VALUE

------------------------------ --------------

NLS_LANGUAGE ITALIAN

NLS_TERRITORY ITALY

NLS_CURRENCY <euro sign here>

NLS_ISO_CURRENCY ITALY

.....

5) If NLS_LANG is specified without the <Language>_<Territory> part then the <Language>_<Territory> part defaults to AMERICAN_AMERICA.

So if you set NLS_LANG=.WE8MSWIN1252 then you get this:

PARAMETER VALUE

------------------------------ ----------

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

....

6) If the NLS_LANG is set (either like in point 3, 4 or 5) then parameters like

NLS_SORT, NLS_DATE_FORMAT, etc. can be set as a "standalone" setting and will overrule the defaults derived from NLS_LANG <Language>_<Territory> part.

So if you set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 and NLS_ISO_CURRENCY=FRANCE then you get this:

PARAMETER VALUE

------------------------------ -----------

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY FRANCE

...

Defaults:

---------

* If NLS_DATE_LANGUAGE or NLS_SORT are not set then they are derived from

NLS_LANGUAGE.

* If NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTERS are not set then they are derived from NLS_TERRITORY

7) If the NLS_LANG is not set at all, then it defaults to

<Language>_<Territory>.US7ASCII and the values for the

<Language>_<Territory> part used are the ones found in

NLS_INSTANCE_PARAMETERS. Parameters like NLS_SORT defined as "standalone" on the client side are ignored.

Top of the Document

Instance Parameters

SELECT * from NLS_INSTANCE_PARAMETERS;

These are the settings in the init.ora of the database at the moment that the database was started or set through ALTER SYSTEM.

If the parameter is not explicitly set in the init.ora or defined by ALTER SYSTEM then its value is NOT derived from a "higher" parameter (we are talking about parameters like NLS_SORT that derive a default from NLS_LANGUAGE in NLS_SESSION_PARAMETERS, this is NOT the case for NLS_INSTANCE_PARAMETERS)

Top of the Document

Database Parameters

SELECT * from NLS_DATABASE_PARAMETERS;

Defaults to AMERICAN_AMERICA if there are no parameters explicitly set in the init.ora during database creation time. If there is parameters set in the init.ora during database creation you see them here. There is no way to change these after the database creation. Do NOT attempt to update system tables to bypass these settings! These settings are used to give the database a default if the INSTANCE and SESSION parameters are not set.

Additional SELECT statements:

A) SELECT name,value$ from sys.props$ where name like '%NLS%';

This gives the same info as NLS_DATABASE_PARAMETERS.

You should use NLS_DATABASE_PARAMETERS instead of props$.

Note the UPPERCASE '%NLS%'

B) SELECT * from v$nls_parameters;

This view shows the current session parameters and the *DATABASE* characterset as seen in the NLS_DATABASE_PARAMETERS view.

C) SELECT name,value from v$parameter where name like '%NLS%';

This view gives the same information as NLS_INSTANCE_PARAMETERS.

Note the LOWERCASE '%NLS%'

D) SELECT userenv ('language') from dual;

and

SELECT sys_context('userenv','language') from dual;

Both these SELECT statements give the session's <Language>_<territory> and the

DATABASE character set. The database character set is not the same as the character set of the NLS_LANG that you started this connection with! So don't be fooled, although the output of this query looks like the value of a NLS_LANG variable, it is NOT.

E) SELECT userenv ('lang') from dual;

This SELECT gives the short code that Oracle uses for the Language defined by NLS_LANGUAGE setting for this session. If NLS_LANGUAGE is set to French then this will return "F", if NLS_LANGUAGE is set to English then this will return "GB"

If NLS_LANGUAGE is set to American then this will return "US", and so on...

F) SHOW parameter NLS%

This will give the same as the NLS_INSTANCE_PARAMETERS

Top of the Document

An example of a wrong NLS_LANG setup

A database is created on a UNIX system with the US7ASCII character set. A Windows client connecting to the database works with the WE8MSWIN1252 character set (regional settings -> Western Europe /ACP 1252) and the DBA, use the UNIX shell (ROMAN8) to work on the database. The NLS_LANG is set to american_america.US7ASCII on the clients and the server.

A very important point (as mentioned before):

When the client NLS_LANG character set is set to the same value as the database character set, Oracle assumes that the data being sent or received are of the same (correct) encoding, so no conversions or validations may occur for performance reasons. The data is just stored as delivered by the client, bit by bit.

 From Windows insert an ‘é’ (LATIN SMALL LETTER E WITH ACUTE) into a table NLS_TEST containing one column ‘TEST’ of the type 'char'.

As long as you insert into and select from the column on Windows with the WE8MSWIN1252 character set everything runs smoothly. No conversion is done and 8 bits are inserted and read back, even if the character set of the database is defined as 7 bits. This happens because a byte is 8 bits and Oracle is ALWAYS using 8 bits even with a 7 bit character set. In a correct setup the most Significant Bit is just not used and only 7 bits are taken into account.

For one reason or another you need to insert from the UNIX server. When you SELECT from tables where data is inserted by the Windows clients you get a ‘Ò’ (LATIN CAPITAL LETTER O WITH TILDE) instead of the ‘é’. 

If you insert ‘é’ on the UNIX server and you SELECT the row at the Windows client you get an ‘Å’ (LATIN CAPITAL LETTER A WITH RING ABOVE) back.

Bottom line is that you have INCORRECT data in the database. You store the numeric value for ‘é’ of the WE8MSWIN1252 character set in the database but you tell Oracle this is US7ASCII data, so Oracle is NOT converting anything and just stores the numeric value (again: Oracle thinks that the client is giving US7ASCII codes because the NLS_LANG is set to US7ASCII, and the database character set is also US7ASCII -> no conversion done).

 When you SELECT the same column back on the UNIX server, Oracle is again expecting that the value is correct and passes the value to the UNIX terminal without any conversion.

Now the problem is that in the WE8MSWIN1252 character set the ‘é’ has the hexadecimal value 'E9’and in the Roman8 character set the hexadecimal value for ‘é’ is 'C5'.  Oracle just passes the value stored in the database ('E9') to the UNIX terminal, and the UNIX terminal thinks this is the letter ‘?’ because in its (Roman8) character set the hexadecimal value 'E9' is representing the letter ‘Ò’.  So instead of the ‘é’ you get ‘Ò’ on the UNIX terminal screen.

 The inverse (the insert on the UNIX and the SELECT on the Windows client) is the same story, but you get other results.

 The solution is creating the database with a character set that contains ‘é’

(WE8MSWIN1252, WE8ISO89859P1, UTF-8, etc.) and setting the NLS_LANG on the client to WE8MSWIN1252 and on the server to WE8ROMAN8. If you then insert an ‘é’ on both sides, you will get an ‘é’ back regardless of where you SELECT them. Oracle knows then that a hexadecimal value of 'C5’ inserted by the UNIX and an 'E9’ from a WE8MSWIN1252 client are both ‘é’ and inserts ‘é’ into the database (the code in the database depends on the character set you have chosen). 

 You don't have to switch between UNIX, Windows or other OS clients to run into this kind of problem.  The same problem appears if you add Windows clients that are using another character set and have an incorrect NLS_LANG set.

Top of the Document

How to setup the NLS_LANG Properly for UNIX

To specify the locale behavior of your client Oracle software, you have to set your NLS_LANG parameter. It sets the language, territory and also the character set of your client. You need to check the locale environment settings to set your NLS_LANG 3rd field (character set) in accordance with it. To do this, use the "locale" command like this:

$ locale

The output of this command is not exactly the same on all the Unix environments. On some platforms, it can be useful to use the following syntax to have more details about the codepage really used:

$ locale LC_CTYPE | head

Example of output in a HP-UX environment:

""

""

"iso885915"

""

Example of output in a Linux environment:

upper;lower;alpha;digit;xdigit;space;print;graph;blank;cntrl;

punct;alnum;

In these cases, the NLS_LANG 3rd field should be set to WE8ISO8859P15. On Solaris, AIX, TRU64, this syntax doesn't give interesting complementary information. To find more details about these settings:
On Solaris, look in /usr/lib/locale
On AIX, look in /usr/lib/nls/README
On TRU64, look in /usr/lib/nls
On HP-UX, look in /usr/lib/nls/config
On Linux, look in /usr/share/locale/locale.alias

To set a chosen value for these "locale" settings, it's needed to know which values are available. To know that, use the following syntax:

$ locale -a

Then, when you have chosen a value, for example UTF-8 on Linux, you can set it like this:

$ export LC_ALL=UTF-8

or

% setenv LC_ALL UTF-8

In this case, the 3rd field (character set) of NLS_LANG should be set to UTF8.

% setenv NLS_LANG American_America.UTF8

Top of the Document

How to setup the NLS_LANG Properly for Windows and DOS Code Pages

On Windows systems, the encoding scheme (character set) is specified by a code page. Code pages are defined to support specific languages or groups of languages, which share common writing systems. From Oracle point of view the terms code page and character set mean the same. Note that in non Chinese-Japanese-Korean environments, the Windows GUI and DOS command prompt do not use the same code page.

As a result Windows uses 2 different character sets for the ANSI (sqlplusw.exe) and the OEM (dos box - sqlplus.exe) environments.

Where to set the NLS_LANG in Windows

In the Registry:

On Windows systems, you should make sure that you have set an NLS_LANG registry subkey for each of your Oracle Homes:

You can easily modify this subkey with the Windows Registry Editor:

Start -> Run...

Type "regedit", and click "ok"

Edit the following registry entry:

For Oracle version 7:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

For Oracle Database versions 8, 8i and 9i:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx\

where "x" is the unique number identifying the Oracle home.

HOME0 is the first installation

For Oracle Database 10g:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_<oracle_home_name>

There you have an entry with name NLS_LANG

When starting an Oracle tools, like SQL*Plusw, it will read the content of the oracle.key file located in the same directory to determine which registry tree will be used, therefore which NLS_LANG subkey will be used.

As a System or User Environment Variable, in System properties:

Although the Registry is the primary repository for settings on Windows, it is not the only place where parameters can be set. Even if not at all recommended, you can set the NLS_LANG as a System or User Environment Variable in the System properties.

This setting will be used for ALL Oracle homes.

To check and modify them:

Right-click the 'My Computericon -> 'Properties'

Select the 'Advanced Tab -> Click on 'Environment Variables'

The 'User Variables list contains the settings for the specific OS user currently logged on and the 'System variables system-wide variables for all users.

Since these environment variables take precedence over the parameters already set in your Registry, you should not set Oracle parameters at this location unless you have a very good reason.

As an Environment variable defined in the command prompt:

Before using an Oracle command line tool you need to MANUALLY SET the NLS_LANG parameter. In an MS-DOS command prompt, use the set command, for example:

C:\> set NLS_LANG=american_america.WE8PC850

Top of the Document

Determine your Windows ANSI code page

Now that you know what the NLS_LANG is currently set to you can check to see if it properly agrees with the current ANSI code page. The ACP (ANSI Code Page) is defined by the "default locale" setting of Windows, so if you have a UK Windows 2000 client and you want to input Cyrillic (Russian) you need to change the ACP (by changing the "default locale") in order to be able to input Russian.

You'll find its value in the registry:

Start -> Run...

Type "regedit", and click "ok"

Browse the following registry entry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\NLS\CodePage\

There you have (all the way down) an entry with as name ACP. The value of ACP is your current GUI Codepage, for the mapping to the Oracle name. Since there are many registry entries with very similar names, please make sure that you are looking at the right place in the registry.

Additionally, the following URL provides a list of the default code pages for all Windows versions:

http://www.microsoft.com/globaldev/reference/ (under the REFERENCE tab on the left of the page)

OEM = the command line codepage, ANSI = the GUI codepage

Note that the Honk Kong HKSCS is listed here: http://www.microsoft.com/hk/hkscs/

Find the correspondent Oracle client character set:

Find the Oracle client character set in the table below based on the ACP you found above. Note that there is only ONE CORRECT value for a given ACP.

This is the character set used by the GUI SQL*Plus (sqlplusW.exe/ plus80W.exe / plus33W.exe ) that you start through the Windows start menu. Please note the difference between the GUI SQL*Plus and the "DOS mode" SQL*Plus.

You can use UTF8 as Oracle client character set (=NLS_LANG) on Windows NT, 2000 and XP but you will be limited to use only client programs that explicitly support this configuration. This is because the user interface of Win32 is not UTF8, therefore the client program have to perform explicit conversions between UTF8 (used on Oracle side) and UTF16 (used on Win32 side).

Set it in your Registry:

Use the Windows Registry Editor to set up the NLS_LANG in your Oracle Home with the value you have just found above.

Top of the Document

The correct NLS_LANG for Windows Command Line Operations

MS-DOS mode uses, with a few exceptions like CJK, (Japanese, Korean, Simplified Chinese, and Traditional Chinese) a different code page (called OEM code page) than Windows GUI (ANSI code page). Meaning that before using an Oracle command line tool such as SQL*Plus (sqlplus.exe/ plus80.exe / plus33.exe ) en svrmgrl in a command prompt then you need to MANUALLY SET the NLS_LANG parameter as an environment variable with the set DOS command BEFORE using the tool.

For Japanese, Korean, Simplified Chinese, and Traditional Chinese, the MS-DOS OEM code page (CJK) is identical to the ANSI code page meaning that, in this particular case, there is no need to set the NLS_LANG parameter in MS-DOS mode.

In all other cases, you need to set it in order to overwrite the NLS_LANG registry key already matching the ANSI code page. The new "MS-DOS dedicated" NLS_LANG needs to match the MS-DOS OEM code page that could be retrieved by typing chcp in a Command Prompt:

C:\> chcp

Active code page: 437

C:\> set NLS_LANG=american_america.US8PC437

If the NLS_LANG parameter for the MS-DOS mode session is not set appropriately, error messages and data can be corrupted due to incorrect character set conversion.

Use the following list to find the Oracle character set that fits to your MS-DOS code page in use on your locale system: