Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 <= 4000).

Why does Oracle call this datatype VARCHAR2 and not just VARCHAR like other DBMSs?

share|improve this question
add comment

3 Answers

up vote 18 down vote accepted

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.

share|improve this answer
    
I remember varchar2 in Oracle 8i, but I'm not sure if it was the same a varchar then. –  bernd_k Feb 17 '11 at 18:22
    
    
There are indications that at some point before 8i the definition may have been different. –  Leigh Riffel Feb 17 '11 at 18:52
    
I used to think there was a different length limit on varchar before version 8, but I can't find anything reputable to back this up and now wonder if it is just a myth. –  Jack Douglas Apr 3 '12 at 11:31
    
@Jack Douglas - I can't find anything in the Oracle 7 documentation about that. From docs.oracle.com/cd/A57673_01/DOC/dcommon/oin/indexj.htm >> Oracle 7 Server >> Server Concepts >> Oracle Datatypes (can't directly link to it) says pretty much the same thing as later versions. In another section of the version 7 documentation it says that in version 6 Varchar and Varchar2 were synonyms for char. –  Leigh Riffel Apr 3 '12 at 13:17
show 1 more comment

Because in the original SQL standard a VARCHAR was 255 characters, and Oracle were at least having a stab at standards compliance in those days.

share|improve this answer
    
That gives hope to see Oracle varchar3(max) in some years. –  bernd_k Feb 17 '11 at 18:02
    
Sql-server had no problems increasing the allowed length from 255 to 8000 (and now even to max). Why did Oracle need a different datatype. –  bernd_k Feb 17 '11 at 18:06
    
There is no need for a varchar(max), just use a CLOB –  a_horse_with_no_name Mar 4 '12 at 16:30
2  
CLOB has different semantics to VARCHAR2 –  Gaius Mar 6 '12 at 9:48
add comment

Currently, the two are synonymous.

VARCHAR is an ANSI standard data type but Oracle's implementation of the VARCHAR data type violates the ANSI standard by considering the empty string to be NULL (Oracle's implementation predates the ANSI standard). As Leigh points out, Oracle has stated that the semantics of the VARCHAR data type may change in the future with respect to how the empty string is treated. If and when that happens, the semantics of the VARCHAR2 data type will remain the same. Using the VARCHAR2 data type is safer because you don't have to worry that some future version of Oracle will break your code by causing empty strings to no longer be considered NULL.

share|improve this answer
1  
+1 for mentioning the NULL/empty thing. –  Martin Aug 9 '11 at 6:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.