➤ EMP DEPT Tables Queries
➤ Set Operators in Oracle
➤ CREATE command
➤ RENAME command
➤ DROP command
➤ Default Value in Column
➤ ABS() in Oracle SQL
➤ CEIL() in Oracle SQL
➤ SYSDATE in Oracle
➤ Trunc Oracle date
➤ TO_CHAR() in Oracle
➤ GROUP BY Clause
➤ Having Clause
➤ ORDER BY Clause
➤ Constraints in Oracle
➤ Rename Constraint
➤ Disable Constraint
➤ Drop Constraint
➤ NOT NULL Constraint
➤ UNIQUE Constraint
➤ PRIMARY KEY Constraint
➤ FOREIGN KEY Constraint
➤ CHECK Constraint
➤ Unlock User in SQL Plus
➤ Find SID in Oracle database
➤ Check Database Version
➤ Check Database Size
➤ Error ORA 01031
Varchar Vs Varchar2 in Oracle | Both varchar and varchar2 data types in the Oracle database are used to store the dynamic length of alpha-numeric values. But there are some differences between them.
The varchar data type is an ANSI standard data type that works with all relational database products (Oracle, MySQL, PostgreSQL, and e.t.c.). It stores up to 2000 bytes. Whereas varchar2 data type is an Oracle standard data type that works only in the Oracle database. It stores up to 4000 bytes.
| VARCHAR | VARCHAR2 |
|---|---|
| It is ANSI standard data type. | It is an Oracle standard data type. |
| It works in all relational database products like Oracle, MySQL, PostgreSQL, and e.t.c. | It works only in the Oracle database. |
| Varchar stores a minimum of 1 byte to a maximum of 2000 bytes. | Varchar2 stores a minimum of 1 byte to a maximum of 4000 bytes |
| Varchar can identify NULL and empty strings separately. | Varchar2 cannot identify NULL and empty strings separately. |
| Prior to Oracle 7.0, for storing dynamic length alpha-numeric data Oracle provided varchar data type. | Oracle 7.0 introduced the varchar2 data type. |
Varchar is reserved by Oracle to support the distinction between NULL and an empty string in the future, as the ANSI standard prescribes. Varchar2 does not distinguish between a NULL and an empty string, and never will. If you rely on the empty string and NULL being the same thing, you should use varchar2.
Note:- From Oracle 7.0 onwards whenever we are creating a varchar data type then the Oracle server internally automatically converts the varchar data type into the varchar2 data type.
Let us demonstrate it through an example. First, we will create a table having a column with the varchar data type.
CREATE TABLE TEST(NAME VARCHAR(10));Here we created a table TEST having the column “NAME”. We tried to create a column with a varchar data type to store a maximum of 10 alpha-numeric values.
The desc command can be used to get the description/information of the table, its all column names, and corresponding data types.
DESC TEST;Output:-
Name Null? Type
-------------------- -------- ----------
NAME VARCHAR2(10)We tried to create the “NAME” column of the “TEST” table using varchar data type but internally Oracle server converts varchar data type into varchar2 datatype.
Also See:- Char vs Varchar in SQL
If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!
