How Oracle converts between Character Sets

In Oracle 19c, converting between different national character sets involves internal character set conversion mechanisms managed by the Oracle Database Globalization Support (NLS).
National Character Set Conversion Process in Oracle 19c
Oracle supports conversion between different national character sets when data moves between different databases, clients, and storage formats. The key components involved in this process are:

1. National Character Set Storage (NCHAR, NVARCHAR2, NCLOB)
   • Unlike CHAR and VARCHAR2, which use the database’s primary character set (NLS_CHARACTERSET), the national character set (NLS_NCHAR_CHARACTERSET) stores data for NCHAR, NVARCHAR2, and NCLOB data types.
   • Oracle supports AL16UTF16 and UTF8 as national character sets.
2. Implicit and Explicit Character Set Conversion
   • Implicit Conversion: Happens when Oracle automatically converts data between the database character set and the national character set (e.g., when fetching NVARCHAR2 data into a VARCHAR2 variable).
   • Explicit Conversion: Developers can use functions like CONVERT() or NLS_CHARSET_CONVERT().
3. Using CONVERT() Function
   • The CONVERT() function allows converting between two character sets:
     

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

   • The above query converts a string from UTF8 to AL16UTF16.
4. Using NLS_CHARSET_CONVERT() for Blob/Large Data
   • NLS_CHARSET_CONVERT() is useful for binary-to-character conversions:
     

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

   • Converts a string between UTF8 and AL16UTF16.
5. ALTER DATABASE CONVERT TO CHARACTER SET (Not for National Character Set)
   • You can change the database character set but not directly the national character set.
   • If migration to a different national character set is needed, data export/import (Data Pump or traditional export/import) must be used.

How Oracle Handles Conversion Internally

• Oracle maps characters based on Unicode encoding rules and applies conversion tables to transform the data.
• If an unsupported character exists in the target character set, replacement characters (such as ? or _) may appear.
• Performance considerations: Character set conversions can increase CPU usage, especially for large-scale transactions.

Best Practices for Character Set Conversion in Oracle 19c

1. Ensure the target character set supports all source characters to prevent data loss.
2. Use Unicode (AL32UTF8 or AL16UTF16) when possible to avoid conversion issues.
3. Minimize implicit conversions by using explicit functions (CONVERT() or NLS_CHARSET_CONVERT()).
4. Use Oracle Data Pump (expdp/impdp) for bulk migration to a different national character set.

Your Oracle database also includes a number of ways to convert data from one national character set to another.
Conversion functions
If you wish to convert data in a SQL statement from one character set to another, you use the CONVERT function. The syntax for the function is

CONVERT(char, dest_char_set, [src_char_set])

where char is the character string to be converted, dest_char_set is the destination character set, and src_char_set is an optional parameter for the source character set. If this parameter is not included, Oracle assumes that the default character set for the server is the source character set. You can also use the TRANSLATE USING function to translate values into either the database character set or the national character set for the database. The syntax for the function is

TRANSLATE text USING CHAR_CS/NCHAR_CS

where text is the character string you wish to translate into a different character set;

1. CHAR_CS indicates that you want to convert the string to the database character set, with an output type of VARCHAR2; and
2. NCHAR_CS indicates that you want to convert the string to the national character set, with an output type of NVARCHAR2.

• Other Conversion Functions
  There are several other conversion functions you may use for specific types of conversions:
  1. The standard TO_CHAR, TO_DATE, and TO_NUMBER functions can have an optional parameter specified to indicate that the source is in a particular national character set.
  2. The NLS_UPPER, NLS_LOWER, and NLS_INITCAP functions take into account the fact that certain national character sets have different rules for uppercase, lowercase, and initial capitalization. These functions take these differences into account when you specify an NLS_SORT parameter.
  3. NLS_SORT is used when you want to compare values based on their order in the specified national language set. Normally, comparisons in a WHERE clause are made with binary values. If your national language was German and you wanted to compare two values based on their German sort order, you would use the syntax WHERE NLS_SORT(value1) > NLS_SORT(value2).
  
  The following series of images shows how the conversion process with NLS_SORT would operate:

NLS (National Language Support) parameters determine the locale-specific behavior on both the client and the server. NLS parameters can be specified in the following ways:

1. As initialization parameters on the server
   You can include parameters in the initialization parameter file to specify a default session NLS environment. These settings have no effect on the client side; they control only the server's behavior. For example:
   

   NLS_TERRITORY = "CZECH REPUBLIC"
   

2. As environment variables on the client, you can use NLS environment variables, which may be platform-dependent, to specify locale-dependent behavior for the client and also to override the default values set for the session in the initialization parameter file. For example, on a UNIX system:
   

   % setenv NLS_SORT FRENCH
   

3. With the ALTER SESSION statement
   You can use NLS parameters that are set in an ALTER SESSION statement to override the default values that are set for the session in the initialization parameter file or set by the client with environment variables.
   

   ALTER SESSION SET NLS_SORT = FRENCH;
   

The next lesson is the module conclusion.