What is the difference between Oracle's VARCHAR and VARCHAR2 datatypes? When migrating tables coming from other DBMSs to Oracle, one of the standard tasks is to replace all VARCHAR(n) fields with VARCHAR2(n) fields (provided n &lt;= 4000).

Why does Oracle call this datatype VARCHAR2 and not just VARCHAR like other DBMSs?
It would seem that Oracle at one time had plans to give a different definition to VARCHAR than to VARCHAR2.  It has told customers this and recommends against using VARCHAR.  Whatever their plans were, as of 11.2.0.2 VARCHAR is identical to VARCHAR2.  Here is what the SQL Language Reference 11g Release 2 says:


  Do not use the VARCHAR data type. Use
  the VARCHAR2 data type instead.
  Although the VARCHAR data type is
  currently synonymous with VARCHAR2,
  the VARCHAR data type is scheduled to
  be redefined as a separate data type
  used for variable-length character
  strings compared with different
  comparison semantics.


The PL/SQL User's Guide and Reference 10g Release 2 puts it this way:


  Currently, VARCHAR is synonymous with
  VARCHAR2. However, in future releases
  of PL/SQL, to accommodate emerging SQL
  standards, VARCHAR might become a
  separate datatype with different
  comparison semantics. It is a good
  idea to use VARCHAR2 rather than
  VARCHAR.


The Database Concepts 10g Release 2 document says the same thing in stronger terms:


  The VARCHAR datatype is synonymous
  with the VARCHAR2 datatype. To avoid
  possible changes in behavior, always
  use the VARCHAR2 datatype to store
  variable-length character strings.


The Oracle 9.2 and 8.1.7 documentation say essentially the same thing, so even though Oracle continually discourages the use of VARCHAR, so far they haven't done anything to change it's parity with VARCHAR2.