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 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,
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;
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.
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 firstname.lastname@example.org. Again I appreciate your visit. Hope to see you again and again!