is this still correct for 12.1.0.2.0?
A reader, September 01, 2017 - 2:59 pm UTC
Is this still the correct answer for Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
September 02, 2017 - 2:39 am UTC
Yes, it is. Why would you think it were not, when the documentation section pointed to by Tom in 12.2 docs is exactly the same.
They no longer appear to be the same....
A reader, August 09, 2018 - 7:23 pm UTC
August 10, 2018 - 8:30 am UTC
But they still are:
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. https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Data-Types.html#GUID-DF7E10FC-A461-4325-A295-3FD4D150809E create table t (
c1 varchar(32767),
c2 varchar2(32767)
);
select column_name, data_type, data_length
from user_tab_cols
where table_name = 'T'
COLUMN_NAME DATA_TYPE DATA_LENGTH
C1 VARCHAR2 32767
C2 VARCHAR2 32767
No difference?
David DUBOIS, August 10, 2018 - 10:49 am UTC
Hello,
I had a long formation to become an Oracle DBA and I asked exactly the same question to the teacher. He thought a lot and said "If you have two columns, COL1 VARCHAR2(50) and COL2 VARCHAR(50), you write "TEST" inside them, there is a difference. Oracle knows in the column's header of COL1 that the length is 4 but, for COL2, it has to read all the characters to have this information because there is nothing in the column's header about this length.
August 10, 2018 - 2:32 pm UTC
I'm not sure what you're saying there?
Anyway, as the doc quote and example above show, varchar is currently a synonym for varchar2. So there is no difference.
A reader, August 10, 2018 - 5:01 pm UTC
OK, sorry for my english, I retry :-)
He said : there is a difference in the column header, for VARCHAR2 you have the information about how many characters were entered in the column. But, for VARCHAR, this information es missing and you have to read the column to know this number of characters.
I think it was the area "Column length" in this picture
http://dbaarea.ca/index.php/2016/11/18/how-to-calculate-row-size/ But maybe he was wrong...
August 16, 2018 - 12:03 am UTC
Your DBA is mistaken. Both varchar and varchar2 will be stored in the same way
Characterizations, not answers
Kadri KZ, December 01, 2018 - 5:52 pm UTC
I came across this thread and just noticed the tone of meanness in them. Either the question or, worse yet, the person that asks the question is characterized and chastised.
Instead of saying "As of this time, there is no difference between the two data types," the supplier of the question is in a way reminded that they have not carefully studied the documentation.
Here's a suggestion: If the documentation provided a complete and thorough explanation, the question would not arise, would it? So the documentation says that there is no difference. But then why do we have VARCHAR2 in the first place? Clearly the community wants a deeper understanding here.
December 03, 2018 - 3:25 am UTC
I would respectfully suggest you've misinterpreted our response.
I said: "currently, there is no difference....". The link to the documentation is not a "RTFM" but a reference so that if the poster wants
a) more detail, or
b) a reference to point to when talking about this with others, or
c) a point to come back to should this change in a future release.
VARCHAR is a subtype of VARCHAR2
David DUBOIS, December 03, 2018 - 11:34 am UTC
Hello,
Just a precision, if you go inside $ORACLE_HOME/rdbms/admin/stdspec.sql you can see :
"type VARCHAR2 is NEW CHAR_BASE;
subtype VARCHAR is VARCHAR2;"
So VARCHAR inherit his properties from VARCHAR2.
Story of a bug in VARCHAR
Higaion Lopes, December 16, 2022 - 7:25 pm UTC
Tom, there is a story of a bug in VARCHAR that's why they invented VARCHAR2, i'm correct?
December 21, 2022 - 7:08 am UTC
I think that's an apocryphal story.