Oracle Database - NLS_LANG (LOCALE)

1 - About

NLS_LANG is an environment parameter used to set the locale of a client application.

Setting the NLS_LANG environment parameter is the simplest way to specify locale behaviour for Oracle Database software. It sets the language and territory used by the client application. It also sets the client's character set, which is the character set for data entered or displayed by a client program such as SQL*Plus.

NLS_LANG is not a NLS parameter, it's an oracle environment variable set:

  • as an environment variable on UNIX platforms.
  • as a key in the registry on Windows platforms.

3 - Syntax

The NLS_LANG environment variable uses the following format:

[NLS_LANG]=[NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]

where:

  • 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.
  • character set specifies the encoding used by the client application (normally the Oracle character set that corresponds to the character set of the user terminal or the operating system)

The NLS_LANG environment variable is set as a local environment variable for the shell on all UNIX-based platforms. For example, if the operating system locale setting is en_US.UTF-8, then the corresponding NLS_LANG environment variable should be set to AMERICAN_AMERICA.AL32UTF8.

4 - How to determine the NLS_LANG ...

The NLS_LANG character set should reflect the setting of the operating system character set of the client. For example, if the database character set is AL32UTF8 and the client is running on a Windows operating system, then you should not set AL32UTF8 as the client character set in the NLS_LANG parameter because there are no UTF-8 WIN32 clients. Instead, the NLS_LANG setting should reflect the code page of the client. For example, on an English Windows client, the code page is 1252. An appropriate setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252.

Setting NLS_LANG correctly enables proper conversion from the client operating system character set to the database character set.

When these settings are the same, Oracle Database assumes that the data being sent or received is encoded in the same character set as the database character set, so character set validation or conversion may not be performed. This can lead to corrupt data if the client code page and the database character set are different and conversions are necessary.

5 - Example

The following table lists some of the valid values for the NLS_LANG environment variable.

Operating system locale NLS_LANG values
French (France) FRENCH_FRANCE.WE8ISO8859P15, FRENCH_FRANCE.WE8ISO8859P1, FRENCH_FRANCE.WE8MSWIN1252, FRENCH_FRANCE.AL32UTF8
Japanese (Japan) JAPANESE_JAPAN.JA16EUC, JAPANESE_JAPAN.JA16SJIS, JAPANESE_JAPAN.AL32UTF8

Refer to the operating system specific documentation on how to determine the operating system locale environment setting.

6 - Documentation / Reference

db/oracle/nls_lang.txt ยท Last modified: 2017/09/13 16:12 by gerardnico