Oracle NCHAR Data Type

1.    It is useful to store UNICODE character values with fixed length.

2.    Max. size is 2000 chars/bytes.

3.    You specify the national character set as either AL16UTF16 or UTF8

4.    when you create your database, You specify the national character set as either AL16UTF16 or UTF8.

5.    When you create a table with an NCHAR column, you specify the column length as size characters, or more precisely, code points in the national character set.

6.    One code point has always 2 bytes in AL16UTF16 and from 1 to 3 bytes in UTF8, depending on the particular character encoded by the code point.

                 Create table test (description nchar(20));

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

The maximum value of size is 1000 characters when the national character set is AL16UTF16, and 2000 characters when the national character set is UTF8. However, independently, the absolute maximum length of any character value that can be stored into an NCHAR column is 2000 bytes.

 For example

• Even if you define the column length to be 1000 characters, Oracle returns an error if you try to insert a 1000-character value but the national character set is UTF8 and all code points are 3 bytes wide. 
• The value of size is a length constraint, not guaranteed capacity.
•  If you want an NCHAR column to be always able to store size characters in both national character sets, use a value of size that is less than or equal to 666.

  Data conversion between CHAR and NCHAR

• If you assign a CHAR value to an NCHAR column, the value is implicitly converted from the database character set to the national character set. 
• If you assign an NCHAR value to a CHAR column, the value is implicitly converted from the national character set to the database character set.
•  If some of the characters from the NCHAR value cannot be represented in the database character set, then if the value of the session parameter NLS_NCHAR_CONV_EXCP is TRUE, then Oracle reports an error.
•  If the value of the parameter is FALSE, non-representable characters are replaced with the default replacement character of the database character set, which is usually the question mark '?' or the inverted question mark '¿'.