Summary: in this tutorial, you will learn about the Oracle CHAR
data type which is a fixed-length character string type.
Introduction to Oracle CHAR
data type
The Oracle CHAR
data type allows you to store fixed-length character strings. The CHAR
data type can store a character string with the size from 1 to 2000 bytes.
To define a CHAR
column, you need to specify a string length either in bytes or characters as shown following:
CHAR(length BYTE) CHAR(length CHAR)
If you don’t explicitly specify BYTE
or CHAR
followed the length
, Oracle uses the BYTE
by default.
The default value of length
is 1 if you skip it like the following example:
column_name CHAR
When you insert or update a fixed-length character string column, Oracle stores the characters as the fixed-length data. It means that if you store a value whose length is less than the maximum length defined in the column, Oracle pads the spaces to the character string up to the maximum length. In case you insert a value whose length is larger than the column, Oracle returns an error.
Oracle uses blank-padded comparison semantics for comparing CHAR
values.
Oracle CHAR
examples
Let’s take a look some examples to understand how the CHAR
data type works.
A) Space usage example
First, create a new table named t
that consists of a CHAR
column (x
) and VARCHAR2
column (y
). The length of each column is 10 bytes.
CREATE TABLE t (
x CHAR(10),
y VARCHAR2(10)
);
Second, insert a new row into the t
table with the same data for both x
and y
columns:
INSERT INTO t(x, y )
VALUES('Oracle', 'Oracle');
Third, verify the insert by using the following query:
SELECT
*
FROM
t

The following statement retrieves data from the t
table:
SELECT
x,
DUMP(x),
y,
DUMP(y)
FROM
t;

In this example, we used the
function to return the detailed information on DUMP()
x
and y
columns:
The string Oracle
takes 6 bytes. However, Oracle padded 4 more spaces on the right of the string to make its length 10 bytes for the x
column. It is not the case for the y
column because the data type of y
column is a variable-length character string (VARCHAR2
).
It is more clear if you use the
function to get the number of bytes used by the LENGTHB()
x
and y
columns:
SELECT
LENGTHB(x),
LENGTHB(y)
FROM
t;

B) Characters comparison example
The following statements return the same result:
SELECT * FROM t WHERE x = 'Oracle';
SELECT * FROM t WHERE y = 'Oracle';
However, if you use bind variables, the effect is different. Consider the following example:
SQL> variable v varchar2(10) SQL> exec :v := 'Oracle'; PL/SQL procedure successfully completed.
In this code block, we declared v
as a bind variable with the VARCHAR2
data type.
Now, we use v
as an input to compare against the the x
column:
SQL> select * from t where x = :v;
no rows selected
The statement returned an empty result set.
The following query uses the v
variable to compare with the y
column:
SQL> select * from t where y = :v;
X Y
---------- ----------
Oracle Oracle
It returned a row as expected.
This is because when comparing the string of character types with unequal length, Oracle uses non-blank-padding semantics.
To make it work, you need to use the RTRIM()
function to strip spaces from the CHAR
data before comparing it with the input string as follows:
SQL> select * from t where rtrim(x) = :v;
X Y
---------- ----------
Oracle Oracle
In this tutorial, you have learned about the Oracle CHAR
data type and understood the behaviors of the CHAR
columns in terms of space usages and character comparisons.