The VARCHAR2 Datatype

VARCHAR2 variables store variable-length character strings. When you declare a variable-length string, you must also specify a maximum length for the string, which can range from 1 to 32767 bytes. You may specify the maximum length in terms of characters or bytes, but either way the length is ultimately defined in bytes. The general format for aVARCHAR2 declaration is:

variable_name VARCHAR2 (max_length [CHAR | BYTE]);

where:

variable_name

Is the name of the variable you wish to declare.

max_length

Is the maximum length of the variable.

CHAR

Indicates that max_length is expressed in terms of characters.

BYTE

Indicates that max_length represents a number of bytes.

When you specify the maximum length of a VARCHAR2 string in terms of characters (using the CHAR qualifier), the actual length in bytes is determined using the largest number of bytes that the database character set uses to represent a character. For example, the Unicode UTF-8 character set uses up to three bytes for some characters; thus, if UTF-8 is your underlying character set, declaring a VARCHAR2 variable with a maximum length of 100 characters is equivalent to declaring the same variable with a maximum length of 300 bytes.

If you omit the CHAR or BYTE qualifier when declaring a VARCHAR2 variable, then whether the size is in characters or bytes depends on the NLS_LENGTH_SEMANTICS parameter. NLS_LENGTH_SEMANTICS defaults to BYTE, so you'll find in practice that omitting the qualifier usually results in byte-sized variables.

Following are some examples of VARCHAR2 declarations:

DECLARE small_string VARCHAR2(4); line_of_text VARCHAR2(2000); feature_name VARCHAR2(100 BYTE); -- 100 byte string emp_name VARCHAR2(30 CHAR); -- 30 character string

The maximum length allowed for PL/SQL VARCHAR2 variables is 32,767 bytes, a much higher maximum than that for the VARCHAR2 datatype in the Oracle RDBMS (2000 bytes prior to Oracle8i, and 4000 bytes in Oracle8i and above). This size limit applies regardless of whether you declare the variable's size in terms of characters or bytes. As a result of PL/SQL's much higher size limit, if you plan to store a PL/SQL VARCHAR2 value into a VARCHAR2 database column, you must remember that only the first 2000 or 4000 bytes can be inserted, depending on which release of Oracle you are using. Neither PL/SQL nor SQL automatically resolves this inconsistency.

If you need to work with strings greater than 4000 bytes in length (or 2000 prior to Oracle8i), consider storing those strings in CLOB (character large object) columns. See Chapter 12 for information on CLOBs.