Character- Tables and Constraints

Use a character data type to store characters and string data. The following character data types are available in Oracle:
• VARCHAR2
• CHAR
• NVARCHAR2 and NCHAR

VARCHAR

The VARCHAR2 data type is what you should use in most scenarios to hold character/ string data. A VARCHAR2 allocates space based only on the number or characters in the string. If you insert a one-character string into a column defined to be VARCHAR2(30), Oracle will consume space for only the one character. The following example verifies this behavior:

Here is a snippet of the output, verifying that only 1 byte has been allocated:

Note Are VARCHAR and VARCHAR2 the same? VARCHAR is reserved for the ANSI standard and distinguishes between NULL and an empty string, and in Oracle it can change. VARCHAR2 is an Oracle standard; it will behave the same and does not change. It does not distinguish between an empty string and NULL.

When you define a VARCHAR2 column, you must specify a length. There are two ways to do this: BYTE and CHAR. BYTE specifies the maximum length of the string in bytes, whereas CHAR specifies the maximum number of characters. For example, to specify a string that contains at most 30 bytes, you define it as follows:

varchar2(30 byte)

Many DBAs do not realize that if you do not specify either BYTE or CHAR, then the default length is calculated in bytes. In other words, VARCHAR2(30) is the same as VARCHAR2(30 byte). If you specify VARCHAR2(30 char), you can always store 30 characters in the string, regardless of whether some characters require more than 1 byte.

As demonstrated, the size of the column is only what is stored, and to support multiple bytes, it is easier to just adjust to a larger value for the VARCHAR2 and use the default for bytes.

CHAR

In almost every scenario, a VARCHAR2 is preferable to a CHAR. The VARCHAR2 data type is more flexible and space efficient than CHAR. This is because a CHAR is a fixed-length character field. If you define a CHAR(30) and insert a string that consists of only one character, Oracle will allocate 30 bytes of space. This can be an inefficient use of space and can be difficult in matching because of the inserted spaces. If using CHAR, it does

make sense to use it only if the size of the value will not change and is absolutely static in size. CHAR might have been good for a flag, but now you have the BOOLEAN data type to choose too.