Oracle NCHAR

Summary: in this tutorial, you will learn about the Oracle NCHAR data type and the differences between NCHAR and CHAR.

Overview of Oracle NCHAR data type

The Oracle NCHAR datatype is used to store fixed-length Unicode character data. The character set of NCHAR can only be AL16UTF16 or UTF8, which is specified at the database creation time as the national character set.

When you create a table with an NCHAR column, the maximum size of the NCHAR column is always in the character length semantics, for example:

CREATE TABLE nchar_demo ( description NCHAR(10) );
Code language: SQL (Structured Query Language) (sql)

In this example, the maximum length of the description column is 10 characters. It is not possible to use the byte length for maximum size of the NCHAR columns like the following:

description NCHAR(10 BYTE) -- not possible
Code language: SQL (Structured Query Language) (sql)

The maximum byte length of a NCHAR column depends on the current national character set. It is the product of the maximum character length and the maximum number of bytes in each character.

To find the current national character set, you use the following statement:

SELECT * FROM nls_database_parameters WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';
Code language: SQL (Structured Query Language) (sql)
Oracle NCHAR default national character set

The AL16UTF16 character set uses 2 bytes for storing a character so the description column has the maximum byte length of 20 bytes.

Oracle limits the maximum length of the NCHAR column to 2000 bytes. It means that an NCHAR column can only hold up to 2000 characters for 1-byte characters or 1000 characters for 2-byte characters.

Oracle NCHAR vs. CHAR

First, the maximum size of NCHAR is only in the character length semantics while the maximum size of CHAR can be in either character or byte length semantics.

Second, NCHAR stores characters in national default character set whereas the CHAR stores characters in the default character set.

The following statement returns the default character set used by CHAR and default national character set used by NCHAR:

SELECT * FROM nls_database_parameters WHERE PARAMETER IN( 'NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET' );
Code language: SQL (Structured Query Language) (sql)
Oracle NCHAR - default character sets

In this tutorial, you have learned about Oracle NCHAR data type and the differences between NCHAR and CHAR.

Was this tutorial helpful?