Difference between CHAR and VARCHAR2 In Oracle

Oracle database contains different types of Character data types.  Among those data types, CHAR and VARCHAR2 are widely utilized compared to other types such as VARCHAR, CLOB etc. in PL/SQL programming. Due to this it becomes evident to understand the difference between CHAR and VARCHAR2. Additionally, this is the most frequently asked interview question of Oracle database.

Difference between CHAR and VARCHAR2

VARCHAR2

CHAR

VARCHAR2 has maximum length of 32767 bytes CHAR has maximum length of 2000 bytes
Space is not padded to the values for unused length A space is padded to the right if length of variable is more than value passed to the variable
VARCHAR2 is used mainly for variable length character string CHAR is generally used for fixed length character string, for example, Pin code
It is mandatory to specify length in VARCHAR2 By default the length of CHAR is 1 if it is not specified

Difference between CHAR and VARCHAR2 with example

Let us declare two variables with same string and different data types,

For example,

DECLARE
  lv_char      CHAR(8) := 'Hello';
  lv_varchar2  VARCHAR2(8) := 'Hello';
  len_char     NUMBER(2);
  len_varchar2 NUMBER(2);
BEGIN
  len_char     := LENGTH(lv_char);
  len_varchar2 := LENGTH(lv_varchar2);
  DBMS_OUTPUT.PUT_LINE('Length of CHAR -> ' || len_char);
  DBMS_OUTPUT.PUT_LINE('Length of VARCHAR2 -> ' || len_varchar2);
END;

Output

Length of CHAR -> 8
Length of VARCHAR2 -> 5

Which data type is better?

It is a long going myth among some programmers that performance of CHAR is better than VARCHAR2. There is no impact on performance if you have used VARCHAR2 for fixed length character string.

I personally would recommend using VARCHAR2 instead of CHAR because it will help you save waste of unnecessary disk space in case you are incorrect about fixed length character string.

Hope you like this article and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of Oraclemine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.