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 .
The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum column length (in bytes, not characters) between 1 and 2000 for the VARCHAR2 column.
The VARCHAR2 data type specifies a variable-length character string in the database character set. You specify the database character set when you create your database. When you create a table with a VARCHAR2 column, you must specify the column length as size optionally followed by a length qualifier.
VARCHAR2 vs. NVARCHAR2. First, the maximum size of VARCHAR2 can be in either bytes or characters, whereas the maximum size of NVARCHAR2 is only in characters. In addition, the maximum byte length of an NVARCHAR2 depends on the configured national character set.
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
.
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
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> 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> create table test (my_char varchar(20));
Table created.
SQL> 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
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:-
NULL
s for both VARCHAR
and VARCHAR2
.The historical reason for these two keywords is explained well in an answer to a different question.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With