CLOBs and NCLOBs character set storage in Oracle Release 8i, 9i, 10g and higher
(Doc ID 257772.1)
Last updated on FEBRUARY 02, 2022
Oracle Database - Enterprise Edition - Version 18.104.22.168 and later Information in this document applies to any platform.
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
Also refer to appendix A of Oracle Globalization Support Guide for a list of varying-width multibyte character sets.
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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!