Lesson 1

For much of the computing world, English is the assumed default. Oracle databases, however, routinely store and process data in dozens of other languages at once — Japanese product names alongside German invoices alongside Arabic customer notes, all in the same table. The mechanism that makes this possible is Oracle's Globalization Support framework, historically abbreviated NLS (National Language Support).

This lesson covers:

1. How Oracle supports other languages at the architectural level
2. How the NLS_LANG client setting controls a session's locale
3. How database character sets work
4. How national character sets (NCHAR/NVARCHAR2) differ from the database character set
5. How to convert and manipulate strings across national language sets

With these five pieces in place, you can build applications that serve virtually any language your users read — without maintaining separate databases or bolting on translation layers.

Every Oracle database has two character sets working at the same time:

• A database character set, which stores CHAR, VARCHAR2, LONG, and CLOB data, and is also used for SQL and PL/SQL source code, and for most comparisons and sorting.
• A national character set, which stores NCHAR, NVARCHAR2, and NCLOB data specifically.

Both are chosen when the database is created and are effectively permanent decisions — changing either one later is a significant, disruptive operation, so getting this right up front matters more than almost any other globalization choice you'll make.

The modern default for the database character set is AL32UTF8, Oracle's implementation of Unicode (UTF-8). Because Unicode covers virtually every written language in one character set, AL32UTF8 lets you store Japanese, Arabic, German, and English data in the same column without juggling multiple databases or per-region character sets. Oracle still supports the older single-byte and double-byte character sets (Western European variants, Shift-JIS-family sets for Japanese, GBK-family sets for Chinese, and so on) for compatibility with existing systems and legacy data feeds, but for anything new, Unicode is the practical starting point.

While the database character set is fixed at creation, each client connecting to the database tells Oracle how to interpret and display data through the NLS_LANG environment variable (or, on Windows, the equivalent registry setting). Its format is:

NLS_LANG = language_territory.charset

Each of the three parts controls something different:

• Language — controls Oracle error messages, sort order, and day/month names.
• Territory — controls date, numeric, and monetary formatting conventions.
• Charset — tells Oracle what character encoding the client's terminal or application actually uses, so incoming and outgoing bytes are interpreted correctly.

A typical modern setting looks like this:

NLS_LANG=JAPANESE_JAPAN.AL32UTF8

Older systems built before Unicode became standard may still specify a legacy double-byte charset instead — NLS_LANG=JAPANESE_JAPAN.JA16SJIS, for example — and Oracle continues to honor that setting for compatibility. New work, though, should default to a Unicode charset unless there's a specific reason not to.

When a client connects, if NLS_LANG is set, Oracle uses it to run an implicit ALTER SESSION that synchronizes the session's locale to match. This mostly matters for OCI-based clients — SQL*Plus, SQLcl, and applications built on the Oracle Call Interface. Java applications using the JDBC Thin driver generally do not read NLS_LANG at all; they rely on the JVM's own locale handling or explicit session-level settings instead. This is a common source of confusion: setting NLS_LANG in a terminal has no effect on a Java application's behavior unless that application is specifically built to honor it.

It's easy to check what character set a live session is actually using, which is useful when troubleshooting garbled data:

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

The client_charset column reports exactly what was picked up from NLS_LANG (or from an equivalent low-level call in the driver), so this is the fastest way to confirm a session's locale without guessing from application logs.

Property	Description
Parameter type	String
Syntax	NLS_LANGUAGE = language
Default value	Operating system-dependent, derived from the NLS_LANG environment variable
Modifiable	ALTER SESSION
Modifiable in a PDB	Yes
Range of values	Any valid language name
Basic	Yes

NLS_LANGUAGE is the server-side initialization parameter behind the "language" portion of NLS_LANG. It controls Oracle's messages, day and month names, the AD/BC/a.m./p.m. symbols, and the default sort order — and it also seeds the default values of two related parameters, NLS_DATE_LANGUAGE and NLS_SORT.

In practice, this initialization parameter is rarely the value actually in effect. If a client connects using the Oracle JDBC driver, or is OCI-based with NLS_LANG defined, the client-side setting overrides it — so NLS_LANGUAGE's server-side default ends up being little more than a fallback for connections that specify nothing else.

To see every NLS parameter's current effective value for your session at once, query V$NLS_PARAMETERS:

SELECT parameter, value
FROM v$nls_parameters
ORDER BY parameter;

This view covers NLS_LANGUAGE, NLS_TERRITORY, NLS_CHARACTERSET, NLS_NCHAR_CHARACTERSET, and every other NLS_* parameter in one place. A companion view, V$NLS_VALID_VALUES, lists every legal value for language, sort, territory, and character set parameters — and flags, per value, whether it has been deprecated, which is a handy sanity check before hardcoding a language or territory name into an application.

The national character set is a second, independent character set chosen at database creation, used only for the NCHAR, NVARCHAR2, and NCLOB data types. It exists to solve a specific problem: guaranteeing Unicode storage for a set of columns even when the database's main character set is not Unicode.

Oracle represents this internally using longer, fixed-width character encodings than the ordinary single- or variable-byte encodings used elsewhere, which is what lets NCHAR/NVARCHAR2 columns reliably hold Japanese, Arabic, or any other non-Latin script regardless of what the rest of the database is configured to store. In modern Oracle databases, the national character set is effectively always one of the Unicode options (typically AL16UTF16), which keeps the guarantee simple: if a column is declared NCHAR or NVARCHAR2, it's Unicode, full stop.

Character-set-aware string handling matters most when you're slicing or measuring strings that may contain multi-byte characters. Cutting a string at the wrong byte boundary can corrupt a multi-byte character instead of cleanly separating it — which is why Oracle provides several variants of the same basic operation, each measuring "length" or "position" a different way:

• SUBSTR — the standard, character-based substring function for most everyday use.
• SUBSTRB — measures and cuts by raw bytes, not characters. Useful when working with byte-oriented protocols or storage limits, but risky on multi-byte data since it can split a character mid-encoding.
• SUBSTRC — measures by complete Unicode characters, including combining character sequences (like a base letter plus an accent mark stored as two code points) treated as one logical unit.
• SUBSTR2 — measures by UCS2 code points, useful when interoperating with systems built around the older fixed 16-bit Unicode representation.
• SUBSTR4 — measures by UCS4 code points, the full 32-bit Unicode representation, which correctly handles characters outside the Basic Multilingual Plane (many emoji and some historic and East Asian scripts fall here).

For converting a string from one character set's encoding to another entirely, Oracle provides the CONVERT function:

SELECT CONVERT('Some text', 'AL32UTF8', 'WE8ISO8859P1') FROM dual;

This re-encodes the literal from the source character set (the third argument) into the target character set (the second argument) — the kind of operation you'll reach for when migrating legacy, single-byte-encoded data into a Unicode column.

Between the SUBSTR family for safe slicing and CONVERT for re-encoding, you have what you need to manipulate multi-language text without accidentally corrupting it — which is the practical payoff of everything covered in this lesson.

In the next lesson, we will discuss how Oracle implements national language support in more depth, including how character set choice interacts with indexing and sort performance.