Oracle NLS_LANG Parameter

A user can set a language environment for his or her own runtime environment through the use of the NLS_LANG parameter. The `NLS_LANG` parameter is an important environment variable in Oracle that specifies the language, territory, and character set used by a client application. It affects how data is displayed, stored, and interpreted in a particular session. Here's how a user can set the `NLS_LANG` parameter for their runtime environment:

1. Understanding `NLS_LANG` Structure:
   The `NLS_LANG` parameter has the following structure:
   

   NLS_LANG = language_territory.characterset
   

   
   
   • `language`: Specifies the language for Oracle messages and day/month names. Example: `AMERICAN`, `FRENCH`.
   • `territory`: Defines the conventions for date and numeric formats. Example: `AMERICA`, `FRANCE`.
   • `characterset`: Specifies the character set used by the client application. Example: `WE8ISO8859P1`, `AL32UTF8`.
2. Setting the `NLS_LANG` Parameter:
   The `NLS_LANG` parameter can be set in the environment of the operating system, typically before starting the Oracle client application. Here’s how to do it on different operating systems:
   On Unix/Linux: You can set `NLS_LANG` in the shell environment using the `export` command:
   

   export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
   

   
   On Windows: You can set `NLS_LANG` in the Command Prompt or permanently in the System Environment Variables:
   Using Command Prompt: cmd

   set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
   

   
   Using System Environment Variables:
   1. Go to Control Panel > System > Advanced System Settings.
   2. Click on "Environment Variables".
   3. Under "System Variables" or "User Variables", click "New".
   4. Add `NLS_LANG` as the variable name and the desired value (e.g., `AMERICAN_AMERICA.AL32UTF8`) as the variable value.
3. Verifying the Setting: After setting `NLS_LANG`, you can verify that it is correctly set by running:
   On Unix/Linux:

   echo $NLS_LANG
   

   
   On Windows: (cmd)

   echo %NLS_LANG%
   

4. Impact of `NLS_LANG`:
   • Language: Determines the language for Oracle messages and the day and month names.
   • Territory: Influences default date and numeric formats.
   • Character Set: Defines the character encoding used by the client, ensuring that characters are correctly interpreted and stored.
5. Example of Use: If you want your client environment to use American English with U.S. date formats and UTF-8 character encoding, you would set:
   

   NLS_LANG=AMERICAN_AMERICA.AL32UTF8
   

This setup would ensure that your Oracle session handles data according to these regional settings and character encoding. By properly setting `NLS_LANG`, users can ensure that their Oracle client applications interact correctly with the database, particularly in terms of language, territory-based formatting, and character set compatibility.

There are three components of the NLS_LANG parameter:

1. Language determines the language used by Oracle for error messages, day names, and month names.
2. Territory determines the local conventions, such as date formats and currency symbols.
3. Charset indicates the actual character set used by the client.

The language and territory portions of the parameter are optional. If a language or territory is not specified, these portions default to American. However, if you have a language setting, you must also have a territory setting and separate the two with an underscore. The territory value is separated from the charset value by a period.

• Example:
  If you wanted to set NLS_LANG to support Canadian French, you would use the strin
  

  NLS_LANG = FRENCH_CANADA.WE8DEC
  

• Setting NLS_LANG:
  You set the NLS_LANG parameter as an environment variable for your particular operating system. If you do not specify a value for the NLS_LANG parameter, Oracle will use the default value for language and territory for the database you are using, which are specified in the initialization file for that database with the parameters NLS_LANGUAGE and NLS_TERRITORY, respectively. You can change the value for the NLS_LANGUAGE or the NLS_TERRITORY parameters at any time in a session with the ALTER SESSION command. You have learned some of the important national language parameters, but additional parameters are worth mentioning.

You can use a number of other national language parameters to indicate specific attributes of the national language setting:

1. NLS_CALENDAR indicates the calendar system used for the language.
2. NLS_CURRENCY indicates the local currency symbol.
3. NLS_DATE_FORMAT indicates the local date format.
4. NLS_DATE_LANGUAGE indicates the default language used for dates.
5. NLS_ISO_CURRENCY indicates the ISO international currency symbol.
6. NLS_SORT indicates the sort order for the language.

NLS_CALENDAR specifies which calendar system Oracle uses. It can have one of the following values:

1. Arabic Hijrah
2. English Hijrah
3. Gregorian
4. Japanese Imperial
5. Persian
6. ROC Official (Republic of China)
7. Thai Buddha

For example, suppose NLS_CALENDAR is set to "Japanese Imperial", the date format is "E YY-MM-DD".
("E" is the date format element for the abbreviated era name.) If the date is May 15, 1997, then the SYSDATE is displayed as follows:

SELECT SYSDATE FROM DUAL;
SYSDATE
--------
H 09-05-15

Each of these can be set in the Oracle initialization file or on a session-by-session basis. You do not have to set any of them unless you wish their values to be different from the normal values for NLS_LANGUAGE and NLS_TERRITORY.
In the next lesson, you will learn about character sets, which determine how data is stored and interpreted in the Oracle database.

Click the Quiz link below to test your knowledge of national language support.
National Language Support - Quiz