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 the 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)

The AL16UTF16 character set uses 2 bytes for storing a character so the description column has a 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 the 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)

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