Oracle NLS_LANG Parameter

The previous lesson covered the parameters that live inside the database — NLS_LANGUAGE, NLS_TERRITORY, and the rest of the server-side defaults. NLS_LANG is different: it's a client-side environment variable, set outside the database entirely, that tells Oracle how your session should behave the moment you connect. It's the single most common way an individual user selects a runtime environment without touching any server configuration.

Structure. NLS_LANG is built from three parts, in a fixed order:

NLS_LANG = language_territory.charset

• Language — controls the language used for Oracle messages, and for day and month names (examples: AMERICAN, FRENCH).
• Territory — controls date and numeric formatting conventions (examples: AMERICA, FRANCE).
• Charset — the character encoding your client actually uses, so Oracle interprets incoming and outgoing bytes correctly (example: AL32UTF8, Oracle's Unicode/UTF-8 implementation and the practical default for new work).

Setting NLS_LANG. It's set as an operating-system environment variable, before the client application starts:

On Unix/Linux, using the shell's export command:

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

On Windows, either for the current Command Prompt session:

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

or permanently, through System Environment Variables:

1. Go to Control Panel > System > Advanced System Settings.
2. Click "Environment Variables".
3. Under "System Variables" or "User Variables", click "New".
4. Add NLS_LANG as the variable name and a value such as AMERICAN_AMERICA.AL32UTF8.

Verifying the setting. Once set, confirm it directly from the shell:
On Unix/Linux:

echo $NLS_LANG

On Windows (cmd):

echo %NLS_LANG%

That confirms what the client thinks it's sending — but the value Oracle actually applied to a given session is worth checking independently, especially when troubleshooting garbled data. Query it directly from the database side:

SELECT client_charset
FROM v$session_connect_info
WHERE sid = SYS_CONTEXT('USERENV', 'SID');

This is a useful habit: an application server, connection pool, or misconfigured shell profile can silently prevent NLS_LANG from reaching the session it was meant for, and this query is the fastest way to catch that.

What NLS_LANG affects, once it's in effect:

• Language — Oracle's messages and day/month names.
• Territory — default date and numeric formats.
• Character set — how characters are encoded going into and out of the client, so text is stored and displayed correctly rather than corrupted.

Setting NLS_LANG=AMERICAN_AMERICA.AL32UTF8, for instance, gives you American English messaging, U.S.-style date and numeric formatting, and Unicode-safe character handling — a solid default for most new work, regardless of which languages the underlying data eventually needs to hold.

The language and territory portions of NLS_LANG are actually optional. If you omit them, Oracle falls back to American for both. There's one rule to keep in mind, though: if you specify a language, you must also specify a territory, joined by an underscore — you can't set one without the other. The territory and the charset, in turn, are separated by a period.

Example — Canadian French, using a modern Unicode charset:

NLS_LANG = FRENCH_CANADA.AL32UTF8

Older systems may still specify a legacy single-byte charset for the same locale — FRENCH_CANADA.WE8DEC, for example — and Oracle continues to honor that for compatibility with existing installations. New environments should default to a Unicode charset like AL32UTF8 unless there's a specific reason to match a legacy system's encoding.

If NLS_LANG isn't set at all, Oracle falls back to the database's own defaults — the NLS_LANGUAGE and NLS_TERRITORY initialization parameters, set server-side. Either can be overridden mid-session with ALTER SESSION, independent of whatever NLS_LANG originally specified. Beyond language, territory, and charset, several more parameters let you tune specific pieces of the national language setting individually.

Six parameters in particular are worth knowing by name, each controlling one specific aspect of locale behavior:

• NLS_CALENDAR — the calendar system in use.
• NLS_CURRENCY — the local currency symbol.
• NLS_DATE_FORMAT — the default date display format.
• NLS_DATE_LANGUAGE — the language used for spelling out dates.
• NLS_ISO_CURRENCY — the unambiguous ISO 4217 currency symbol.
• NLS_SORT — the collating sequence for comparing and sorting text.

Each can be set in the database's initialization file, or overridden per session with ALTER SESSION — you only need to touch any of them when you want a value different from what NLS_LANGUAGE and NLS_TERRITORY would otherwise supply.

NLS_CALENDAR accepts one of eight calendar systems: Arabic Hijrah, English Hijrah, Ethiopian, Gregorian, Japanese Imperial, Persian, ROC Official (Republic of China), and Thai Buddha. For example, with NLS_CALENDAR set to Japanese Imperial and a date format of "E YY-MM-DD" (where E is the abbreviated era name), May 15, 1997 displays as:

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

That's era H (Heisei), year 9 — 1997 was the ninth year of the Heisei era, which ran from 1989 until the Reiwa era began in 2019.

NLS_CURRENCY and NLS_ISO_CURRENCY solve related but different problems. NLS_CURRENCY supplies the local currency symbol used by the L number format element — but local symbols are ambiguous: a bare $ could mean U.S. dollars, Australian dollars, or several other currencies. NLS_ISO_CURRENCY resolves that ambiguity by returning the unique ISO 4217 code instead, via the C format element. Setting NLS_ISO_CURRENCY = AMERICA, for instance, makes TO_CHAR return the unambiguous 'USD' wherever the C element appears in a format model — useful anywhere financial output needs to be unambiguous across regions, not just readable in one.

NLS_DATE_FORMAT sets the default format TO_CHAR and TO_DATE use for date values, such as "MM/DD/YYYY", while NLS_DATE_LANGUAGE controls what language those dates are spelled out in — day names, month names, and the a.m./p.m./AD/BC abbreviations. The two are independent: you could format a date the U.S. way while spelling the month name in French, or vice versa.

NLS_SORT deserves closer attention, since it affects more than display — it changes query behavior and performance. It controls how character values are compared in ORDER BY, GROUP BY, comparison operators, IN, BETWEEN, LIKE, MIN/MAX, GREATEST/LEAST, and INSTR. Set to BINARY, comparisons run directly on the raw byte values of the character encoding — fast, but not the ordering a human reader of that language expects. Set to a named linguistic sort instead, Oracle applies the ordering rules speakers of that language actually use — the kind of ordering you'd see in a dictionary or phone directory, and often surprising if you're not expecting it (in Spanish, for example, "ch" traditionally sorts as its own unit between "c" and "d"). The tradeoff: a standard index can't serve a linguistically-sorted ORDER BY, so Oracle typically has to perform an explicit sort instead of an index range scan — unless a functional index built on the NLSSORT function exists to restore that index path. Whether a given clause actually honors NLS_SORT at all is itself governed by a separate parameter, NLS_COMP.

Selecting the right runtime environment, in the end, is a matter of layering these choices deliberately rather than accepting whatever an OS or client defaults to: character set for correct storage and display, language and territory for messages and formatting, and calendar or sort order for the specific handful of cases where the defaults genuinely don't fit. In the next lesson, we'll look at character sets themselves — how Oracle stores the data these runtime settings are only ever interpreting.

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