Lesson 6	Character Set Conversion
Objective	Understand how Oracle converts between character sets.

How Oracle Converts Between Character Sets in 26ai

In the last lesson we chose a national character set for NCHAR, NVARCHAR2, and NCLOB columns, separate from the database character set that governs everything else. Whenever data has to move between those two encodings — or between your database and a client, file, or another database entirely — Oracle has to convert it. That conversion process is managed by Oracle Database Globalization Support (NLS), and understanding how it works is what separates "the data happened to display correctly" from "I know why it displayed correctly."

Implicit and Explicit Conversion

Oracle converts character data in two distinct ways. Implicit conversion happens automatically, without you asking for it — for example, when you fetch an NVARCHAR2 value into a plain VARCHAR2 variable, Oracle silently converts it from the national character set to the database character set on your behalf. Explicit conversion is when you call a function yourself to control exactly how and when that conversion happens. Implicit conversion is convenient, but it hides a cost: every silent conversion is CPU work you didn't plan for, and at scale — large-scale transactions, bulk loads, high-throughput queries — that adds up. Reaching for explicit conversion functions when you know a conversion is coming gives you visibility and control that implicit conversion doesn't.

Explicit Conversion Functions

Oracle 26ai gives you several explicit tools, each suited to a slightly different situation.

CONVERT() — general-purpose character set conversion

The most direct tool for converting a string from one character set to another:


CONVERT(char, dest_char_set, [src_char_set])

char is the string to convert, dest_char_set is the character set you want the result in, and src_char_set is optional — if you leave it out, Oracle assumes the server's default character set is the source. For example:


SELECT CONVERT('ΑΒΓ', 'AL16UTF16', 'UTF8') FROM DUAL;

This converts the string from UTF8 into AL16UTF16.

TRANSLATE ... USING — convert toward the database or national character set specifically

Where CONVERT() takes two arbitrary character sets, TRANSLATE ... USING is narrower and more opinionated — it converts a string toward whichever of your two database-level character sets you specify:


TRANSLATE text USING CHAR_CS/NCHAR_CS

text is the string to convert.

CHAR_CS converts to the database character set, returning a VARCHAR2.
NCHAR_CS converts to the national character set, returning an NVARCHAR2.

NLS_CHARSET_CONVERT() — binary-to-character and large-data conversion

Useful specifically for binary-to-character conversions, including larger data:


SELECT NLS_CHARSET_CONVERT('テスト', 'UTF8', 'AL16UTF16') FROM DUAL;

This converts the string between UTF8 and AL16UTF16.

Locale-aware formatting and comparison functions

A few more functions round out the toolbox, though they're less about raw conversion and more about locale-aware behavior:

TO_CHAR, TO_DATE, and TO_NUMBER each accept an optional parameter to indicate the source data is in a particular national character set.
NLS_UPPER, NLS_LOWER, and NLS_INITCAP apply the casing and capitalization rules of a specific locale — some languages capitalize or case differently than English — when you supply an NLS_SORT parameter.
NLS_SORT itself changes how values are compared, not just how they're cased. Ordinary WHERE-clause comparisons use binary values by default. If your users expect German dictionary ordering, you'd instead compare with WHERE NLSSORT(value1) > NLSSORT(value2).

The image below shows exactly what that difference looks like in practice — the same query, filtered two different ways.

Comparison of binary order versus NLS_SORT (German linguistic order) when filtering CUSTOMERS WHERE NAME > 'B' — binary order returns 3 rows including a misplaced German-alphabet 'Alan', while NLSSORT correctly returns 2 rows. — The following comparison shows how `NLS_SORT` changes which rows a `WHERE` clause returns: a normal binary comparison (`SELECT * FROM CUSTOMERS WHERE NAME > 'B'`) versus a linguistic comparison using `NLSSORT` (`SELECT * FROM CUSTOMERS WHERE NLSSORT(NAME) > NLSSORT('B')`). Binary order returns 3 rows, including a German-alphabet "Alan" variant that's misplaced by raw byte comparison; `NLSSORT` correctly returns only 2 rows.

How Oracle Handles Conversion Internally

Under the hood, Oracle maps characters according to Unicode encoding rules and applies conversion tables to transform data from one character set's representation to another's. If the target character set doesn't support a particular source character, Oracle substitutes a replacement character — typically ? or _ — rather than failing outright, which is exactly why choosing character sets that fully cover your data (Lessons 4 and 5) matters more than it might seem. And because every conversion is real computational work, expect measurable CPU overhead on high-volume or large-scale transactions where conversions happen constantly rather than occasionally.

Changing a Character Set After the Fact

You can change the database character set on an existing database with ALTER DATABASE CONVERT TO CHARACTER SET — carefully, and as covered in Lesson 4, not something to do casually. What you cannot do is change the national character set the same way. If you need to migrate to a different national character set, the only supported path is a full data export and re-import, using Data Pump (expdp/impdp) or classic export/import for older environments. That asymmetry is one more reason the national character set decision in Lesson 5 is worth getting right the first time.

Setting NLS Parameters

NLS parameters control locale-specific behavior on both the client and the server, and Oracle gives you three layers to set them at, each overriding the one before it:

Server initialization parameters. Set in the initialization parameter file to define a default session NLS environment. These affect only server-side behavior, never the client:
```
NLS_TERRITORY = "CZECH REPUBLIC"
```
Client environment variables. Platform-dependent settings that control client-side, locale-dependent behavior and override the server's session defaults. On UNIX, for example:
```
% setenv NLS_SORT FRENCH
```
ALTER SESSION. Overrides both the initialization parameter file and any client environment variable, for the current session only:
```
ALTER SESSION SET NLS_SORT = FRENCH;
```

Best Practices for Character Set Conversion

A short list worth keeping in mind whenever conversion is involved:

Confirm the target character set supports every character in your source data, to avoid silent data loss via replacement characters.
Default to Unicode (AL32UTF8 for the database character set, AL16UTF16 for the national character set) wherever possible — it sidesteps most conversion problems before they start.
Minimize reliance on implicit conversion; reach for explicit functions (CONVERT(), TRANSLATE...USING, NLS_CHARSET_CONVERT()) when you know a conversion is coming, so you control it rather than discover it.
For bulk migration to a different national character set, use Oracle Data Pump (expdp/impdp) rather than attempting it in place.

Between choosing your character sets (Lessons 4 and 5) and understanding how Oracle converts between them, you now have the full picture of how Oracle stores and moves multilingual data. The next lesson is the module conclusion.