My Oracle Support Banner

CLOBs and NCLOBs character set storage in Oracle Release 8i, 9i, 10g and higher (Doc ID 257772.1)

Last updated on NOVEMBER 18, 2019

Applies to:

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

Purpose

CLOBs when using fixed width character set NLS_CHARACTERSET are stored in the NLS_CHARACTERSET character set on disk. Examples are WE8MSWIN1252 , AR8ISO8859P6 etc.

However , CLOB's (and NCLOB's) of varying-width character sets (ex: UTF8, AL32UTF8) are stored in an internal fixed-width Unicode character set on disk. When data is fetched from, or written to, disk storage, Oracle converts between the internal  format and the database (CLOB) or the national character (NCLOB) set Oracle uses for the "external" (N)CLOB datatypes.
This can sometimes lead to issues where it's unclear what is exactly being stored on disk. This note explains how this data is stored, and differences between different Oracle versions. Common varying-width database and national character sets are summarized in Table 1.

Table 1: Common varying-width charsets Oracle supports

UTF8
AL32UTF8
JA16EUC
JA16SJIS
ZHT16BIG5
ZHS16GBK
KO16KSC5601

Also refer to appendix A of Oracle Globalization Support Guide for a list of varying-width multibyte character sets.

Scope

This note is intended for anyone wishing to understand the internal storage of CLOB and NCLOB datatypes in varying-width character sets like UTF8 and AL32UTF8.

Note the word "internal" - for any operation on CLOB datatype the NLS_CHARACTERSET is the encoding actually used. This document is purely about the internal way CLOB is stored on disk.

Details

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
Purpose
Scope
Details
 Internal CLOB/NCLOB character sets for varying-width characters sets
 Migrating CLOB and NCLOB data in Oracle Database 10g and higher
 Note on loading CLOBs from files in varying-width character sets
References

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