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.
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.
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:
Ex:
ename varchar2(20)
--------- --------------------
Dileep
Kumar
Kalyan
Dileep Kumar
ram
1. It is used to store fixed length values.
2. By default, the size is 1 character, and max size is 2000 chars or bytes.
3. If you insert a value that is shorter than the column length, then Oracle blank-pads the value to column length.
4. If you try to insert a value that is too long for the column, then Oracle returns an error.
5. A
1-byte string can be inserted into a CHAR(10) column,
but the string is
blank-padded to 10 bytes before it is stored.
6. When
you create a table with a CHAR column, by
default you supply the column length
in bytes. The BYTE qualifier is the same as the
default.
7. If you use the CHAR qualifier, for example CHAR(10 CHAR), then you supply the column length in characters.
Ex:
id char(5)
--------------------
e0001
e0002
e0003
Ex:
FLAG CHAR
----------- ---------
Y
N
Y
N
|
CREATE TABLE test (col1 NUMBER(5,2), col2 FLOAT(5));
INSERT INTO test VALUES (1.23, 1.23); INSERT INTO test VALUES (7.89, 7.89); INSERT INTO test VALUES (12.79, 12.79); INSERT INTO test VALUES (123.45, 123.45);
|