My Oracle Support Banner

How to load Extension B, U+20000 to U+2A6DF “Unicode block” using SQL*Loader (Doc ID 2644338.1)

Last updated on MARCH 04, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Goal

This note helps you in loading Extension B, U+20000 to U+2A6DF “Unicode block” using SQL*Loader.

Some of the data contains Chinese characters. Most of the data with Chinese characters loads fine, but the �� U+299EA does not load correctly.

Database Configuration
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

SQL Loader is configured for CHARACTERSET UTF8

For Example:

Source file   王家��
Loaded in the database by SQL Loader   王家����

The �� U+299EA character is in the CJK Unified Ideographs Extension B, U+20000 to U+2A6DF “Unicode block”. This is a different “Unicode block” than other characters that load correctly.

Charecter   Unicode      Character Name                            Unicode block
      王        U+738B      CJK UNIFIED IDEOGRAPH-738B     CJK Unified Ideographs, U+4E00 to U+9FFF
      家        U+5BB6      CJK UNIFIED IDEOGRAPH-5BB6     CJK Unified Ideographs, U+4E00 to U+9FFF
    ��       U+299EA    CJK UNIFIED IDEOGRAPH-299EA    CJK Unified Ideographs Extension B, U+20000 to U+2A6DF

Only having trouble loading this one character below using SQL Loader. All other characters load fine.

?? U+299EA CJK UNIFIED IDEOGRAPH-299EA

This one character is in a special Extension B Unicode block. The other characters that load successfully are not.

Cut and Paste of this character from a Word document into the table using the Toad table editor or some other application does save the correct character in the table and can able view it using the Toad table viewer.

Setting NLS_LANG=NULL or any other character set may not work 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.