Character Data Types

The Oracle database supports a number of different data types for storing character data. All of these data
types interpret data entered and retrieved as character values, but each of the data types has additional
characteristics as well.

CHARCHAR columns can have a length specification after the data type or are assigned the default length of 1.
A CHAR column will always use the assigned space in the database. If a column is defined as CHAR(25),
every instance of that column will occupy 25 bytes in the database. If the value entered for that column
does not contain 25 bytes, it will be padded with spaces for storage and retrieval.

VARCHAR2 and VARCHARVARCHAR2 columns also can have a length specification, but this data type only stores the number of
characters entered by the user. If you define a column as VARCHAR2(25) and the user only enters 3 char-
acters, the database will only store those three characters. Because of this distinction, there may be some
issues with comparison between a CHAR column and a VARCHAR2 column.
The following SQL code snippet adds the same value, ‘ABC’, to two columns: ABCCHAR, which is described
as a CHAR column with 20 characters, and ABCVARCHAR, which is described as a VARCHAR2 column with 20
characters.

INSERT INTO TEST_TABLE COLUMNS(ABCCHAR, ABCVARCHAR) (‘ABC’, ‘ABC’);
IF ABCCHAR = ABCVARCHAR

This final test will fail, since it is comparing ABCCHAR, which has a value of “ABC                  “ with ABCVARCHAR,
which has a value of “ABC”. These comparison problems could be resolved by truncating the ABCCHAR
column, but it is much easier to just avoid them by using VARCHAR2 for normal character columns. The
VARCHAR2 data type will also save storage space, which improves overall performance.
The VARCHAR data type is synonymous with the VARCHAR2 data type, but Oracle recommends using
VARCHAR2 to protect against possible future changes in the behavior of the VARCHAR data type.

NCHAR and NVARCHAR2The Oracle database can store character data from many different languages, including Chinese or Japanese,
which require 2 bytes of storage for each character. These double-byte languages use the NCHAR and
NVARCHAR2 data types, which allow for the extended storage needed for the characters in these languages.
Prior to Oracle9i, using these data types would automatically indicate that each character required 2
bytes of storage. With Oracle9i, you can specify the storage for a column either in bytes or as a number
of characters. If you define a column with characters, Oracle automatically allocates the appropriate
number of bytes for storage of the data.

LONGThe CHAR and NCHAR data types have a limit of 2,000 characters, while the VARCHAR, VARCHAR2, and
NVARCHAR data types have a limit of 4,000 characters. The LONG data type allows you to enter up to 2 GBs
of data in a single column. The use of the LONG data type has been superceded by the CLOB and NCLOB
data types described next, since they have fewer restrictions on their use than the older LONG data type.

BLOB, CLOB, and NCLOBLike the LONG data type, these three data types, collectively referred to a LOB data types, are designed to
hold more that 4,000 characters. The BLOB data type holds binary data, the CLOB data type holds charac-
ter data, and the NCLOB data type holds double-byte information, just as the NVARCHAR2 data type does.
With Oracle10g, a single CLOB or NCLOB data type can hold up to 128 TBs (!) of data; prior to that release,
the same data types could hold up to 4 GBs of data.
The data for a column of this data type is not stored in an actual row; instead, the column holds a pointer
to the location of the data. LOB columns can participate in transactions, but if you want to manipulate the
data in one of these columns, you have to use specific calls in the PL/SQL built-in package DBMS_LOB. The
use of this package is beyond the scope of this book, since many readers may not be using these long data
types in their applications, but it is covered in the Oracle documentation set.

BFILEThe BFILE data type allows you to have a column that points to a storage location outside of the Oracle
database’s internal storage. You can also attain some of this functionality by using external tables, which
were introduced in Oracle9i. Both BFILEs and external tables can only be used for reading data, and
BFILEs cannot be used in transactions.