What is the difference between varchar and varchar2 in Oracle

Question

What is the difference between varchar and varchar2

User · Answer

VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.
If we declare datatype as VARCHAR then it will occupy space for NULL values. In the case of VARCHAR2 datatype, it will not occupy any space for NULL values. e.g.,

name varchar(10)

will reserve 6 bytes of memory even if the name is 'Ravi__', whereas

name varchar2(10)

will reserve space according to the length of the input string. e.g., 4 bytes of memory for 'Ravi__'.

Here, _ represents NULL.

NOTE: varchar will reserve space for null values and varchar2 will not reserve any space for null values.

User · Answer

After some experimentation  see below   I can confirm that as of September 2017  nothing has changed with regards to the functionality described in the accepted answer -   Rextester demo for Oracle 11g  Empty strings are inserted as NULLs for both VARCHAR and VARCHAR2  LiveSQL demo for Oracle 12c  Same results    The historical reason for these two keywords is explained well in an answer to a different question

User · Answer

As for now  they are synonyms   VARCHAR is reserved by Oracle to support distinction between NULL and empty string in future  as ANSI standard prescribes   VARCHAR2 does not distinguish between a NULL and empty string  and never will   If you rely on empty string and NULL being the same thing  you should use VARCHAR2

User · Answer

Taken from the latest stable Oracle production version 12 2  Data Types  The major difference is that VARCHAR2 is an internal data type and VARCHAR is an external data type   So we need to understand the difference between an internal and external data type     Inside a database  values are stored in columns in tables  Internally  Oracle represents data in particular formats known as internal data types   In general  OCI  Oracle Call Interface  applications do not work with internal data type representations of data  but with host language data types that are predefined by the language in which they are written  When data is transferred between an OCI client application and a database table  the OCI libraries convert the data between internal data types and external data types    External types provide a convenience for the programmer by making it possible to work with host language types instead of proprietary data formats  OCI can perform a wide range of data type conversions when transferring data between an Oracle database and an OCI application  There are more OCI external data types than Oracle internal data types   The VARCHAR2 data type is a variable-length string of characters with a maximum length of 4000 bytes  If the init ora parameter max string size is default  the maximum length of a VARCHAR2 can be 4000 bytes  If the init ora parameter max string size   extended  the maximum length of a VARCHAR2 can be 32767 bytes  The VARCHAR data type stores character strings of varying length  The first 2 bytes contain the length of the character string  and the remaining bytes contain the string  The specified length of the string in a bind or a define call must include the two length bytes  so the largest VARCHAR string that can be received or sent is 65533 bytes long  not 65535   A quick test in a 12 2 database suggests that as an internal data type  Oracle still treats a VARCHAR as a pseudotype for VARCHAR2  It is NOT a SYNONYM which is an actual object type in Oracle   SQL gt  select substr banner 1 80  from v version where rownum 1  Oracle Database 12c Enterprise Edition Release 12 2 0 1 0 - 64bit Production      SQL gt  create table test  my char varchar 20    Table created   SQL gt  desc test Name                 Null     Type MY CHAR                       VARCHAR2 20    There are also some implications of VARCHAR for ProC C   Precompiler options  For programmers who are interested  the link is at  Pro C C   Programmer s Guide

User · Answer

Currently VARCHAR behaves exactly the same as VARCHAR2  However  the type VARCHAR should not be used as it is reserved for future usage   Taken from  Difference Between CHAR  VARCHAR  VARCHAR2

User · Answer

Currently  they are the same  but previously   Somewhere on the net  I read that     VARCHAR is reserved by Oracle to support distinction between NULL and empty string in future  as ANSI standard prescribes    VARCHAR2 does not distinguish between a NULL and empty string  and never will     Also    Emp name varchar 10  -  if you enter value less than 10 digits then remaining space cannot be deleted  it used total of 10 spaces   Emp name varchar2 10  - if you enter value less than 10 digits then remaining space is automatically deleted

[oracle] What is the difference between varchar and varchar2 in Oracle?

Examples related to oracle

Examples related to sqldatatypes