Oracle DBA Lesson 21B — Which Character Set Does a Text Column Use?

Oracle keeps two character-set maps, not one. VARCHAR2 and CHAR use the database character set. NVARCHAR2 and NCHAR use the national character set. The column type chooses the map. The letters in José do not.

Oracle DBA Lesson 21B — Which Character Set Does a Text Column Use?

Lesson 21 is the map itself: a datafile stores bytes, and the database character set is what turns José into those bytes and back. That video stops at one character set. This one is the second lane, and which column type walks on which lane.

Read both names before you pick a type

A database has a database character set and a national character set. They are chosen at CREATE DATABASE. Neither one is a session setting. NLS_LANGUAGE and NLS_DATE_FORMAT do not move a column from one lane to the other.

-- Two lanes. Not one setting with two spellings.
          -- NLS_CHARACTERSET     = CHAR, VARCHAR2, CLOB
          -- NLS_NCHAR_CHARACTERSET = NCHAR, NVARCHAR2, NCLOB
          SELECT parameter, value
          FROM   nls_database_parameters
          WHERE  parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

On a current database those values are AL32UTF8 and AL16UTF16. AL32UTF8 is Oracle's UTF-8 database character set. ASCII stays one byte. is the two bytes c3,a9. José is five bytes. AL16UTF16 is UTF-16 in big-endian byte order. A character in the basic multilingual plane, including every letter of Jos and the ASCII letter A, is two bytes. José on that lane is eight bytes, not five.

The national character set is allowed to be only AL16UTF16 or the older Oracle character set named UTF8. It cannot be AL32UTF8. UTF8 on that list is not a short name for AL32UTF8. It is a different encoding. Supplementary characters are stored as surrogate pairs there, not as the four-byte UTF-8 sequence AL32UTF8 uses. If NLS_NCHAR_CHARACTERSET says UTF8, you do not have a second copy of the database character set. You have the other legal national map.

The datatype chooses the lane

CHAR, VARCHAR2, and CLOB are stored in the database character set. NCHAR, NVARCHAR2, and NCLOB are stored in the national character set. A LONG is the database character set too, and it is the type you are migrating off, not a place to park Unicode. Object names in the dictionary use the database character set. They are not NVARCHAR2.

Jos in a VARCHAR2 column is AL32UTF8 when that is NLS_CHARACTERSET. The accent does not promote the value to the national lane. The same four characters in an NVARCHAR2 column are AL16UTF16 when that is NLS_NCHAR_CHARACTERSET. Same spelling on the screen. Different bytes in the file. The CREATE TABLE line is the decision.

-- Same name, two lanes. The N prefix is the literal's lane,
          -- not a hint about accents. The column type still wins:
          -- Oracle converts on the way in if they differ.
          CREATE TABLE customer_name (
            name_db  VARCHAR2(80 CHAR),
              name_nat NVARCHAR2(80)
              );

A plain literal, 'José', is encoded in the database character set. An N literal, N'José', is encoded in the national character set. Insert either one into either column and Oracle converts when the lanes differ. On AL32UTF8 and AL16UTF16 both maps can hold José, so the character survives. The bytes change. You paid for a conversion to store a name the database character set already knew how to store.

That conversion is where a mixed schema goes wrong. Compare a VARCHAR2 to an NVARCHAR2, or concatenate them, and Oracle converts one side. If the database character set cannot represent a character that exists only on the national lane, the conversion substitutes a replacement. The character is not stored. A predicate that forces the conversion also wraps the column, so a plain index on that column is no longer the access path. Two lanes in one join are a design bug unless you meant the national column for a reason.

The national lane is a side door, not a second default

The national types exist so an older database, still on US7ASCII, WE8ISO8859P1, or WE8MSWIN1252, can keep a few columns in Unicode without a full character-set migration. NVARCHAR2 is that side door. The rest of the database, including the data dictionary and every ordinary VARCHAR2, stays on the set that cannot spell every language. A literal without the N prefix is still the old set. You did not Unicode the application. You Unicode one column, and only when the insert actually used that column.

On a database whose character set is already AL32UTF8, that door is usually the wrong one. VARCHAR2 already stores José, café, and the languages after those. An NVARCHAR2 column adds a second encoding, implicit conversion at every boundary, and a worse byte cost for the same Western European text: two bytes per character in AL16UTF16, against one byte for ASCII and two for in AL32UTF8. Oracle's guidance for a new database is AL32UTF8 as the database character set, and ordinary character columns for application text.

Length on the national lane is declared in characters. You cannot write NVARCHAR2(80 BYTE). The byte cap is still there. With the standard limit, an NVARCHAR2 value may not exceed 4,000 bytes. AL16UTF16 spends two of those bytes on each basic-plane character, so the practical maximum is 2,000 such characters, not 4,000. NCHAR is capped at 2,000 bytes, which is 1,000 characters on AL16UTF16. MAX_STRING_SIZE = EXTENDED raises the byte cap to 32,767. It does not make UTF-16 one byte wide. A VARCHAR2(80 CHAR) on AL32UTF8 is the column that matches the name. An NVARCHAR2 beside it is a second contract.

Ask the column, not the letters

CHARACTER_SET_NAME on the column does not print AL32UTF8. It prints which lane the type uses: CHAR_CS for the database character set, NCHAR_CS for the national character set. You already have the real names from NLS_DATABASE_PARAMETERS. Read them together.

-- CHAR_CS  = NLS_CHARACTERSET        (VARCHAR2, CHAR, CLOB)
          -- NCHAR_CS = NLS_NCHAR_CHARACTERSET  (NVARCHAR2, NCHAR, NCLOB)
          -- CHAR_USED is B or C. National types are character length.
          SELECT column_name, data_type, character_set_name,
                 char_length, char_used
                 FROM   user_tab_columns
                 WHERE  table_name = 'CUSTOMER_NAME'
                 AND    data_type IN ('VARCHAR2','CHAR','CLOB',
                                      'NVARCHAR2','NCHAR','NCLOB')
                                      ORDER  BY column_id;

DUMP shows the bytes of a value and the character set Oracle used for that value. On this database, é in a VARCHAR2 is AL32UTF8 and c3,a9. The same character in an NVARCHAR2 is AL16UTF16 and the two bytes 00,e9. If those do not match the lane you thought you created, the table definition is wrong. The accent on the screen will not tell you.

-- 1016 = hex plus the character set name of the value.
          -- VARCHAR2 é  on AL32UTF8:  c3,a9
          -- NVARCHAR2 é on AL16UTF16: 00,e9
          SELECT DUMP(name_db,  1016) AS db_lane,
                 DUMP(name_nat, 1016) AS national_lane
                 FROM   customer_name
                 WHERE  ROWNUM = 1;

A wrong display is not a wrong column

What SQL*Plus paints is the client declaration applied to whatever bytes came back. NLS_LANG describes the bytes the OCI client is already using. It does not choose VARCHAR2 or NVARCHAR2, and it does not change either character set. Lesson 21 walked the failure in detail: UTF-8 bytes for é declared as WE8ISO8859P1 are stored as two characters, and they read back as ã©. That can happen to a VARCHAR2. It is not evidence the column is national.

JDBC does not read NLS_LANG. A Java string is Unicode in the driver, and the driver converts to the character set of the column it is binding. A VARCHAR2 bind goes to the database character set. An NVARCHAR2 bind goes to the national character set. Setting the variable in the shell and assuming the pool uses it does not pick a lane.

So the check order is fixed. Read NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET. Read CHARACTER_SET_NAME on the column. DUMP one value if the screen looks wrong. Change the client declaration, or the column type, only after those three agree about which one is lying. An accented name on an AL32UTF8 database is not a reason to add NVARCHAR2.

Gotchas

• The datatype picks the lane. Jos in a VARCHAR2 uses NLS_CHARACTERSET. The accent does not move it to NVARCHAR2.
• On AL32UTF8, a regular VARCHAR2 is the usual text column. The national lane is the side door for an older, non-Unicode database character set.
• NCHAR, NVARCHAR2, and NCLOB are the whole national lane. CLOB stays on the database character set. A national column does not retarget the others.
• The national character set is AL16UTF16 or UTF8 only. It is never AL32UTF8. The name UTF8 is not AL32UTF8.
• 'José' is a database-character-set literal. N'José' is a national literal. The column type still converts on insert when the lanes differ.
• In AL16UTF16, José is eight bytes. In AL32UTF8, José is five. NVARCHAR2 length is characters, and the byte cap still binds: 2,000 basic-plane characters at the standard 4,000-byte limit.
• CHARACTER_SET_NAME returns CHAR_CS or NCHAR_CS, not AL32UTF8. Join that to NLS_DATABASE_PARAMETERS in your head, or on paper.
• A screen full of ã© is a client-declaration problem until DUMP says otherwise. It is not a reason to change the column to NVARCHAR2.
• Mixing the lanes in a predicate converts one side and can skip a plain index. If the database character set cannot hold the national character, the replacement character is what you keep.
• Do not treat NVARCHAR2 as a cheaper substitute for migrating the database character set to AL32UTF8. The dictionary and every unprefixed column stay on the old map.

Quick quiz

1. NLS_CHARACTERSET is AL32UTF8. A VARCHAR2 column stores José. Which character set encodes that value?

The national character set, because of the accent The database character set, AL32UTF8 Whatever character set NLS_LANG names UTF8, because that is Oracle's Unicode name

2. Which of these columns uses the national character set?

VARCHAR2 CLOB NVARCHAR2 CHAR

3. The database character set is WE8MSWIN1252. A few columns must store many languages. What is NVARCHAR2 for?

A Unicode lane for those columns only. The other VARCHAR2 columns stay on WE8MSWIN1252. Changing NLS_CHARACTERSET to AL32UTF8 without a migration Making every text column, including the data dictionary, Unicode Fixing a client that displays ã©

4. SQL*Plus shows ã© for a customer name. What do you know about that column?

It is NVARCHAR2. VARCHAR2 cannot store an accent. NLS_CHARACTERSET is US7ASCII. AL32UTF8 rejected é, so the national lane stored it. Nothing about the lane yet. Check CHARACTER_SET_NAME and DUMP. Display also depends on the client.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro