Oracle NLS_LANG: Configuration, Character Sets, Common Values, and Troubleshooting

1. Introduction

Oracle Database provides Globalization Support to help applications work with different languages, regional conventions, and character sets. This support is important in international environments where users enter, retrieve, and display information in different languages, including Arabic, English, Russian, Japanese, and Chinese.

One of the most important client-side configuration settings for Oracle Globalization Support is the NLS_LANG environment variable.

The NLS_LANG setting determines the language used by Oracle client messages, the regional conventions used for dates and numbers, and the character set Oracle assumes is being used by the client application. Correct configuration helps Oracle perform the appropriate character-set conversion when data moves between a client application and the database (Oracle Corporation, n.d.-a).

This article explains the structure of NLS_LANG, the purpose of each component, commonly used values, configuration examples, and practical troubleshooting techniques.

2. What Is NLS_LANG?

NLS_LANG is an environment variable used by Oracle client software to define the client's language, territory, and character-set information.

It is commonly relevant to Oracle client applications and utilities such as SQL*Plus and other applications that use Oracle client libraries.

The general syntax is:

NLS_LANG=language_territory.characterset

For example:

NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

This setting contains three components:

Component	Description
LANGUAGE	Controls the language of Oracle messages, month names, day names, and language-dependent sorting conventions.
TERRITORY	Controls regional conventions such as default date, number, and monetary formats.
CHARACTERSET	Identifies the character encoding used by the Oracle client application.

These components are separated by specific characters:

An underscore (_) separates the language and territory.
A period (.) separates the territory and character set.

The punctuation is significant because Oracle uses it to interpret the individual components (Oracle Corporation, n.d.-a).

3. Understanding the Three NLS_LANG Components

3.1 Language

The language component determines the language used for Oracle messages and language-dependent conventions, including month names, day names, and sorting behavior.

Examples include:

AMERICAN
ARABIC
FRENCH
GERMAN
RUSSIAN
JAPANESE

For example:

NLS_LANG=FRENCH_FRANCE.WE8MSWIN1252

The language component is FRENCH. Oracle can therefore use French-language conventions for supported messages and date-related names.

Changing the language component does not change the database character set or automatically enable the storage of additional characters.

3.2 Territory

The territory component defines regional conventions, particularly default formats for dates, numbers, and currencies.

Examples include:

AMERICA
UNITED KINGDOM
FRANCE
EGYPT
RUSSIA
JAPAN

For example:

NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

The territory is AMERICA.

A different territory may result in different default formatting conventions. For example, date and numeric formatting can differ between American and British regional settings.

The territory component is therefore important when an application relies on Oracle's default NLS formatting behavior.

3.3 Character Set

The character-set component identifies the encoding used by the client application to represent the characters it sends to and receives from Oracle.

Examples include:

WE8MSWIN1252
AR8MSWIN1256
CL8MSWIN1251
JA16SJIS
AL32UTF8

This component is particularly important because an incorrect client character-set declaration can cause character-conversion problems.

For example:

NLS_LANG=ARABIC_EGYPT.AR8MSWIN1256

Here, AR8MSWIN1256 identifies the character set expected by the Oracle client. It does not, by itself, change the database character set.

Oracle uses the client character-set information to determine whether conversion is required when data is transferred between the client and database (Oracle Corporation, n.d.-a).

4. Commonly Used NLS_LANG Values

The following examples illustrate commonly encountered combinations of language, territory, and character set.

Language or Environment	Example NLS_LANG Value
American English	AMERICAN_AMERICA.WE8MSWIN1252
British English	ENGLISH_UNITED KINGDOM.WE8ISO8859P1
Arabic (Egypt)	ARABIC_EGYPT.AR8MSWIN1256
Arabic (United Arab Emirates)	ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256
Brazilian Portuguese	BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252
Russian	RUSSIAN_CIS.CL8MSWIN1251
Japanese	JAPANESE_JAPAN.JA16SJIS
Simplified Chinese	SIMPLIFIED CHINESE_CHINA.ZHS16GBK
Traditional Chinese	TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
Korean	KOREAN_KOREA.KO16MSWIN949

Important: These are illustrative examples, not universal recommendations. The correct character-set component must reflect the actual encoding used by the client application and its operating-system environment. Oracle documents multiple valid combinations and explains that an unsuitable combination can cause character-display or conversion problems (Oracle Corporation, n.d.-a).

The language and territory names must also be valid Oracle locale names. Use the Oracle Globalization Support documentation to verify the supported names and character sets for the relevant Oracle release.

5. NLS_LANG and the Database Character Set

A common misconception is that the character set specified in NLS_LANG must always be identical to the database character set.

This is not correct.

The NLS_LANG character-set component describes the client application's character encoding. The database character set describes how character data is stored in the database.

These two settings can be different.

For example, consider the following environment:

Database character set: AL32UTF8
Windows client code page: 1252
Oracle client character set: WE8MSWIN1252

A possible client configuration is:

NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

Oracle can then perform the required conversion between the client character set and the database character set.

Oracle specifically advises that the client character set should reflect the actual client environment. Setting the client character set incorrectly can cause Oracle to interpret incoming bytes incorrectly, potentially resulting in corrupted or incorrectly displayed data (Oracle Corporation, n.d.-a).

5.1 What Is AL32UTF8?

AL32UTF8 is Oracle's implementation of the Unicode UTF-8 character set. It supports a broad range of international characters and is commonly used for multilingual database environments.

However, selecting AL32UTF8 as the database character set does not mean that every client should automatically use AL32UTF8 in its NLS_LANG setting.

The client-side configuration must still reflect the encoding actually used by the client application. For example, Oracle's documentation explains that a Windows client using code page 1252 should generally identify the corresponding client character set rather than simply copying the database's AL32UTF8 setting (Oracle Corporation, n.d.-a).

6. Configuring NLS_LANG on Windows

On Windows, Oracle client configuration can use the Windows Registry or environment variables, depending on the installation and client configuration.

A typical process for setting a Windows environment variable is:

Open Windows Settings or Control Panel.
Open the system environment-variable settings.
Select the appropriate user or system environment variables.
Create or update the variable named NLS_LANG.
Enter the required value.

Example:

Variable name: NLS_LANG
Variable value: AMERICAN_AMERICA.WE8MSWIN1252

The environment variable should be configured according to the actual client application's character encoding and the intended language and territory.

If the Oracle client installation uses a Registry-based setting, verify the relevant Oracle Home configuration as well. The effective configuration can depend on the client installation and environment.

After changing the setting, restart the relevant client application so that it reads the updated environment.

7. Configuring NLS_LANG on Linux and UNIX

On Linux and UNIX systems, NLS_LANG is commonly configured as an environment variable.

For example, in a Bash shell:

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

To check the current value:

echo $NLS_LANG

The operating-system locale can also help identify the client environment:

locale

For example, a system may report a UTF-8 locale. The Oracle client character set should be selected in a way that correctly represents the encoding used by the client application.

Do not assume that every application uses the same encoding merely because it runs on the same operating system.

8. Checking NLS Settings in Oracle Database

Oracle provides several data dictionary views and functions for inspecting NLS configuration.

8.1 Check Database Character Set

The following query displays the database character set and national character set:

SELECT parameter,
       value
FROM   nls_database_parameters
WHERE  parameter IN (
         'NLS_CHARACTERSET',
         'NLS_NCHAR_CHARACTERSET'
       );

8.2 Check Session NLS Parameters

To inspect the NLS parameters currently applied to the session:

SELECT parameter,
       value
FROM   nls_session_parameters
ORDER BY parameter;

This is useful for checking settings such as:

NLS_LANGUAGE
NLS_TERRITORY
NLS_DATE_FORMAT
NLS_NUMERIC_CHARACTERS
NLS_SORT
NLS_COMP

8.3 Check Instance NLS Parameters

The following query displays NLS parameters configured at the instance level:

SELECT parameter,
       value
FROM   nls_instance_parameters
ORDER BY parameter;

8.4 Check the Session Language and Territory

You can also use:

SELECT USERENV('LANGUAGE')
FROM   dual;

This returns the session language, territory, and database character-set information in a combined value.

Important: This function does not reveal the complete client-side NLS_LANG setting. In particular, it should not be treated as a reliable way to identify the character set declared by the client application (Oracle Corporation, n.d.-b).

9. Common NLS_LANG Problems and Troubleshooting

Incorrect NLS_LANG configuration can cause problems when applications exchange multilingual data with an Oracle database.

9.1 Arabic Characters Display as Question Marks

A typical symptom is that Arabic text appears as question marks or unreadable characters in a client application.

Possible causes include:

The client character set does not support the characters being entered.
The declared client character set does not match the actual client encoding.
The application or terminal does not correctly display the required characters.
Character conversion is occurring incorrectly.

For example, an Arabic Windows client using code page 1256 may require a corresponding Oracle client character set such as:

AR8MSWIN1256

The complete NLS_LANG value must also use an appropriate language and territory.

9.2 Data Appears Correct in One Client but Incorrect in Another

Different client applications or operating systems may use different character encodings.

For example, one application may use a Windows code page while another uses UTF-8.

If the client character-set declaration is incorrect, Oracle may interpret the incoming bytes using the wrong encoding. This can produce inconsistent results between applications.

The appropriate troubleshooting approach is to verify the actual encoding used by each client rather than assuming that all clients should use the same NLS_LANG value.

9.3 Incorrect Date or Number Formats

A different issue occurs when date, numeric, or monetary values are displayed in an unexpected format.

For example, a session may use a date format that differs from the application's expected format.

You can inspect the session settings with:

SELECT parameter,
       value
FROM   nls_session_parameters
WHERE  parameter IN (
         'NLS_DATE_FORMAT',
         'NLS_DATE_LANGUAGE',
         'NLS_NUMERIC_CHARACTERS',
         'NLS_CURRENCY'
       );

For application code, it is generally safer to use explicit format models when converting dates and numbers rather than relying on implicit defaults.

For example:

SELECT TO_CHAR(
         SYSDATE,
         'DD-MON-YYYY',
         'NLS_DATE_LANGUAGE=AMERICAN'
       ) AS formatted_date
FROM dual;

This makes the intended date-language behavior explicit.

9.4 Incorrectly Assuming NLS_LANG Changes the Database Character Set

Changing NLS_LANG does not convert the database to another character set.

It also does not expand the set of characters that the client application can actually represent.

The character-set component is a declaration of the client encoding used for communication with Oracle. It is not a database character-set migration mechanism (Oracle Corporation, n.d.-a).

10. Best Practices

The following practices help reduce globalization and character-conversion problems:

Identify the actual client encoding. Determine the operating-system code page or application encoding before selecting the character-set component.
Do not copy the database character set blindly. The client and database character sets serve different purposes.
Use Unicode where appropriate. For multilingual databases, evaluate AL32UTF8 as a database character set, while configuring each client according to its actual encoding.
Verify the effective session settings. Use NLS_SESSION_PARAMETERS and the other NLS views to inspect the database-side settings.
Test multilingual data. Test Arabic, English, and other required languages through the actual client applications.
Use explicit format models. Avoid depending on implicit date and number formats in application SQL and PL/SQL.
Avoid changing production settings without testing. Incorrect character-set declarations can cause data corruption or inconsistent display behavior.

11. Conclusion

The NLS_LANG environment variable is an important part of Oracle Database Globalization Support. It defines the language, territory, and character-set information associated with an Oracle client application.

Understanding the distinction between these three components is essential when configuring Oracle clients, working with multilingual applications, and troubleshooting character-conversion problems.

The most important practical rule is to configure the client character set according to the actual encoding used by the client application—not simply according to the database character set.

A correct configuration helps Oracle perform appropriate character conversion and supports consistent handling of multilingual data across different client environments.

References

Oracle Corporation. (n.d.-a). Setting up a globalization support environment. Oracle Database Globalization Support Guide. https://docs.oracle.com/en/database/oracle/oracle-database/26/nlspg/setting-up-globalization-support-environment.html

Oracle Corporation. (n.d.-b). NLS_LANG FAQ. https://www.oracle.com/database/technologies/faq-nls-lang.html

Oracle Corporation. (n.d.-c). Configuring locale and character sets using the NLS_LANG environment variable. Oracle Database Client Installation Guide for Microsoft Windows. https://docs.oracle.com/en/database/oracle/oracle-database/18/ntcli/configuring-locale-and-character-sets-using-nls-lang-environment-variable.html

Pages

NLS_LANG

Oracle NLS_LANG: Configuration, Character Sets, Common Values, and Troubleshooting

1. Introduction

2. What Is NLS_LANG?

3. Understanding the Three NLS_LANG Components

3.1 Language

3.2 Territory

3.3 Character Set

4. Commonly Used NLS_LANG Values

5. NLS_LANG and the Database Character Set

5.1 What Is AL32UTF8?

6. Configuring NLS_LANG on Windows

7. Configuring NLS_LANG on Linux and UNIX

8. Checking NLS Settings in Oracle Database

8.1 Check Database Character Set

8.2 Check Session NLS Parameters

8.3 Check Instance NLS Parameters

8.4 Check the Session Language and Territory

9. Common NLS_LANG Problems and Troubleshooting

9.1 Arabic Characters Display as Question Marks

9.2 Data Appears Correct in One Client but Incorrect in Another

9.3 Incorrect Date or Number Formats

9.4 Incorrectly Assuming NLS_LANG Changes the Database Character Set

10. Best Practices

11. Conclusion

References

No comments:

Post a Comment