Oracle Database - Datatype difference between VARCHAR2 and CHAR

Card Puncher Data Processing

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





Discover More
Card Puncher Data Processing
Oracle - DataType

Each value manipulated by Oracle Database has a datatype. Large Object: Data Type Description VARCHAR2(size [BYTE | CHAR]) Variable-length character string having maximum length size...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Card Puncher Data Processing
Oracle Database - Character DataType

You can use the following SQL data types to store alphanumeric data: VARCHAR2 and NVARCHAR2 data types store variable-length character literals. NCHAR and NVARCHAR2 data types store variable-length...



Share this page:
Follow us:
Task Runner