Oracle in a Nutshell

Character Datatypes

Character datatypes can store any string value, including the string representations of numeric values. Assigning a value larger than the length specified for a character datatype results in a runtime error. You can use string functions, such as UPPER, LOWER, SUBSTR, and SOUNDEX, on standard character value types (but not on large character value types, which we describe under “Large Object Datatypes”).

The character datatypes are:

CHAR/CHARACTER[(n [CHAR | BYTE])]

Stores character values with a fixed length. A CHAR datatype can have between 1 and 2000 characters. If you don’t explicitly specify a length for a CHAR, it assumes the default length of 1. If you assign a value that’s shorter than the length specified for the CHAR datatype, Oracle will automatically pad the value with blanks. With Oracle9i, you can specify n with the keywords of BYTE or CHAR.

VARCHAR2(n [CHAR | BYTE]) and VARCHAR(n [CHAR | BYTE])

Store variable-length character strings. Although you must assign a length to a VARCHAR2 datatype, the length is the maximum length for a value, rather than the required length. Values assigned to a VARCHAR2 datatype are not padded with blanks. The VARCHAR2 datatype can have up to 4000 characters.

At this time, the VARCHAR datatype and the VARCHAR2 datatype are synonymous in Oracle8 and later versions, but Oracle recommends the use of VARCHAR2, because future changes may cause VARCHAR and VARCHAR2 to diverge.

With Oracle9i, you can specify n with the keywords ...