By specifying the data type, you allow the database engine to determine the memory allocation of the value, the kind of value you can store, and the actions you can perform on the particular value that is stored there.
Various data types can be used for particular cases, and some may be appropriate for a given type compared to another. Therefore, choosing the correct data type that can store the data type that you desire is necessary.
This post aims to walk you through the fundamentals of working with the VARCHAR2 data type in Oracle databases.
Oracle VARCHAR2 Data Type
The VARCHAR2 data type in Oracle allows us to store the variable-length character strings. This data type can store the strings of up to 4000 characters in length.
It is closely similar to the CHAR data type in Oracle. However, a varchar2 type can store a variable length string, unlike the char data type.
This means that VARCHAR2 only uses the amount of space that is required to store the provided string, while CHAR always uses the maximum specified length even if the provided string is much shorter.
When creating a table column with a VARCHAR2 data type, we must specify the maximum length that can be stored in that column. You can specify the length either as BYTE or a CHAR.
To specify the max length as char, use the following command:
By default, Oracle sets the max length in Bytes.
Once the maximum length is set, you cannot store a string of length which is larger than the determined one. Attempting to do so returns an error.
Oracle VARCHAR2 Max Length
The Oracle database engine uses the MAX_STRING_SIZE parameter to define the maximum size of the VARCHAR2 data type. By default, the maximum size is set to STANDARD which can hold up to 4000 bytes.
You can also change the value of the MAX_STRING_SIZE parameter to EXTENDED, increasing the max length to 32767 bytes.
You can check the current value of the MAX_STRING_SIZE parameter using the following query:
This returns the set value for the max_string_size parameter as follows:
Oracle VARCHAR2 Type
The following example shows an Oracle statement that creates a table with the VARCHAR2 data type:
id NUMBER(5) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
email VARCHAR2(255 char) NOT NULL
);
In this case, the name column can store the VARCHAR2 data types with a max length of 50 bytes.
The email column can store up to 255 characters.
We can insert some sample data into the table as shown in the following:
This inserts the previous record into the customers’ table.
Conclusion
The Oracle VARCHAR2 data type is a versatile and widely-used data type to store the character strings in an Oracle database. It is a variable-length data type that allows you to store the varied-length strings. The VARCHAR2 data type is an efficient choice to store the character strings in an Oracle database as it only uses the space of the provided string rather than a fixed amount of space for all input types.
