Oracle NVARCHAR2 Data Type

1.    To store variable length UNICODE character values.

2.    Max size is 4000 bytes or chars.

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

 

Create a table with an NVARCHAR2 column

CREATE TABLE TEST (

    description NVARCHAR2(50)

);

 

The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit. 

The minimum value of size is 1. The maximum value is:

• 16383 if MAX_STRING_SIZE = EXTENDED and the national character set is AL16UTF16
• 32767 if MAX_STRING_SIZE = EXTENDED and the national character set is UTF8
• 2000 if MAX_STRING_SIZE = STANDARD and the national character set is AL16UTF16
• 4000 if MAX_STRING_SIZE = STANDARD and the national character set is UTF8

Oracle stores a character value in an NVARCHAR2 column exactly as you specify it, without any blank-padding, provided the value does not exceed the length of the column. If you try to insert a value that exceeds the specified length, then Oracle returns an error.

 data conversion VARCHAR2 and NVARCHAR2.

1.    If you assign a VARCHAR2 value to an NVARCHAR2 column, the value is implicitly converted from the database character set to the national character set.

2.    If you assign an NVARCHAR2 value to a VARCHAR2 column, the value is implicitly converted from the national character set to the database character set.

3.    If some of the characters from the NVARCHAR2 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.

4.    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 '¿'.