Anyone who has dealt with the Oracle database knows to declare character fields and variables as
VARCHAR2 , not
But these data types are kind of synonyms:
CREATE TABLE table1 ( id number, text VARCHAR (10), text2 VARCHAR2 (10) ); Name Null Type ----- ---- ------------ ID NUMBER TEXT VARCHAR2 (10) TEXT2 VARCHAR2 (10)
So what’s the difference between them?
Answer 1, authority 100%
They are currently synonymous.
VARCHAR is reserved by
Oracle to support, in the future , the difference between
NULL and an empty string as described in
VARCHAR2 does not distinguish between
NULL and an empty string, and never will.
If you are relying on the empty string and
NULL to be the same, use
translation of the answer @Quassnoi
Answer 2, authority 75%
Currently, no difference. The type
VARCHAR is an alias for
VARCHAR2 and will be replaced with
VARCHAR2 when declared.
VARCHAR is reserved for use in future releases for ANSI / ISO-SQL compliance and Oracle does not recommend this type. Therefore, everyone uses the
VARCHAR2 type because its behavior will never change.
What is the difference between
VARCHAR2 and the standard, there are two of them:
empty string is interpreted as
NULLis ignored during string concantation, in the standard the result of such concantation will be
Why did this happen (I will paraphrase @JustinCave ):
Oracle is a very old vendor in the database market. Back in the days before SQL was standardized, Oracle theorists made the design decision that empty strings in database fields contain no information and are essentially
Over time, when the SQL standard appeared and it was decided that
NULL and an empty string are essentially different, there were already many users with old code that did not take this difference into account. So Oracle was presented with a choice to invalidate existing code or violate the SQL standard. A less destructive decision was made, according to Oracle , to reserve the
VARCHAR datatype to be modified to conform to the SQL standard and introduce the new
VARCHAR2 datatype, immutability whose behavior is guaranteed.
When will the intended behavior changes be made to
This was in version 7 , note the phrase – “in the next version of Oracle7”:
The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. It is recommended that you use VARCHAR2 rather than VARCHAR. In a future version of Oracle7, VARCHAR might be a separate datatype used for variable length character strings compared with different comparison semantics.
And, more than 20 years later, in release 19c :
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.
Correct answer: they do not differ for use; during storage, VARCHAR2 does not reserve space for NULL values, unlike VARCHAR, i.e. for storage (this is important for admins). See the answer at https://stackoverflow.com/questions/ 1171196 / what-is-the-difference-between-varchar-and-varchar2-in-oracle is not correct. There is no difference in working with NULL for VARCHAR or VARCHAR2 (in the future there may be a difference between VARCHAR and VARCHAR2, but now it really is not). The following example illustrates the same behavior:
DECLARE l_str VARCHAR (10): = ''; l_STR2 VARCHAR2 (10): = ''; BEGIN IF l_str IS NULL THEN dbms_output.put_line ('l_str is null'); ELSE dbms_output.put_line ('l_str is not null'); END IF; IF l_str2 IS NULL THEN dbms_output.put_line ('l_str2 is null'); ELSE dbms_output.put_line ('l_str2 is not null'); END IF; END;