Table of Contents

About

Varchar2 and char are two datatype used to store the same type of data: a string. But where are the difference ?

The CHAR data type blank-pads and stores trailing blanks up to a fixed column length for all column values, whereas the VARCHAR2 data type does not add extra blanks. Then to store data more efficiently, use the VARCHAR2 data type.

CHAR datatype
The CHAR datatype specifies a fixed-length character string. Oracle ensures that all values stored in a CHAR column have the length specified by size. If you insert a value that is shorter than the column length, then Oracle blank-pads the value to column length.

VARCHAR2 datatype
The VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length of the column.

Demonstration

The CHAR data type blank-pads and stores trailing blanks up to a fixed column length for all column values, whereas the VARCHAR2 data type does not add extra blanks.

VARCHAR2

DECLARE
   Vs_Test VARCHAR2(10);
BEGIN
   Vs_Test := NULL;
   FOR i In 1..3 
   LOOP
   Vs_Test := Vs_Test || UNISTR( '\00d6' );
   DBMS_OUTPUT.PUT_LINE( i || ' - Length (' || Vs_Test || ') - ' || length(Vs_Test));
   END LOOP;
END;

1 - Length (╓) - 1
2 - Length (╓╓) - 2
3 - Length (╓╓╓) - 3

PL/SQL procedure successfully completed.

CHAR

The same code but with a char type defined. 

DECLARE
   Vs_Test CHAR(10);
BEGIN
   Vs_Test := NULL;
   FOR i In 1..3 
   LOOP
   Vs_Test := Vs_Test || UNISTR( '\00d6' );
   DBMS_OUTPUT.PUT_LINE( i || ' - Length (' || Vs_Test || ') - ' || length(Vs_Test));
   END LOOP;
END;

1 - Length (╓         ) - 10
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 7