NVARCHAR2 Data Type1

 1.    To store variable length UNICODE character values or national character se.

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. 

 

AL16UTF16 and UTF8

 1.    AL16UTF16 and UTF8 are two encoding forms of the Unicode character set (UTF-16 and CESU-8, correspondingly) and hence NVARCHAR2 is a Unicode-only data type.

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

3.    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.

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

5.    If you try to insert a value that exceeds the specified length, then Oracle returns an error.

          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

6.    Independently of the maximum column length in characters, the absolute maximum length of any value that can be stored into an NVARCHAR2 column is 32767 or 4000 bytes, depending on MAX_STRING_SIZE.

7.    For example, even if you define the column length to be 16383 characters, Oracle returns an error if you try to insert a 16383-character value but the national character set is UTF8 and all code points are 3 bytes wide.

8.    The value of size is a length constraint, not guaranteed capacity. 

9. If you want an NVARCHAR2 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 10922, if MAX_STRING_SIZE = EXTENDED, or 1333, if MAX_STRING_SIZE = STANDARD

VARCHAR Data Type

 1.    Do not use the VARCHAR data type.

2.    Use the VARCHAR2 data type instead.

3.    Although the VARCHAR data type is currently synonymous with VARCHAR2, the VARCHAR data type is scheduled to be redefined as a separate data type used for variable-length character strings compared with different comparison semantics.

NCHAR Data Type

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

2.    Max. size is 2000 chars/bytes.

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

AL16UTF16 and UTF8

1.    AL16UTF16 and UTF8 are two encoding forms of the Unicode character set (UTF-16 and CESU-8, correspondingly) and hence NCHAR is a Unicode-only data type.

2.    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.

3.    Oracle ensures that all values stored in an NCHAR column have the length of size characters.

4.     If you insert a value that is shorter than the column length, then Oracle blank-pads the value to the column length.

5.      If you try to insert a value that is too long for the column, then Oracle returns an error. Note that if the national character set is UTF8, blank-padding does not guarantee that all column values have the same byte length.

6.    You can omit size from the column definition. The default value is 1.

7.    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.

8.    However, independently, the absolute maximum length of any character value that can be stored into an NCHAR column is 2000 bytes.

9.    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.

10. The value of size is a length constraint, not guaranteed capacity.

11.  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.

Oracle VARCHAR2 Data Type1

1.     Used to store variable length character values.

2.     No default size. A VARCHAR2 column can store a value that ranges from 1 to 4000 bytes.

3.   When you create a table with a VARCHAR2 column, you must specify the length size    bytes or characters.                  

              VARCHAR2(size BYTE)

              VARCHAR2(size CHAR)

 4.   By default, Oracle uses BYTE if you don’t specify BYTE or CHAR after the size otherwise VARCHAR2(10) column can hold up to 10 bytes of characters.

          For example

1.    if you define a VARCHAR2 column with a maximum size is 20. In a single-byte character set, you can store up to 20 characters. If you store 21 characters or more, Oracle returns an error.

2.    In addition, if you store 10 characters in a VARCHAR2(20) column, Oracle uses only 10 bytes for storage, not 20 bytes. Therefore, using VARCHAR2 data type helps you save spaces used by the table.

           VARCHAR2 Maximum Length

  You must specify size for VARCHAR2. Minimum size is 1 byte or 1 character. Maximum size is:

•   32767 bytes or characters if MAX_STRING_SIZE = EXTENDED
•  4000 bytes or characters if MAX_STRING_SIZE = STANDARD

 

Ex:   
ename   varchar2(20)
---------   --------------------
Dileep
Kumar
Kalyan
Dileep Kumar
ram