Oracle Database

Oracle VARCHAR2 Data Type

Data types are some of the essential blocks in development and database management. Data types are used used to classify the data that is stored and used in a system.

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.

VARCHAR2(max_size BYTE)

To specify the max length as char, use the following command:

VARCHAR2(max_size CHAR)

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:

SHOW PARAMETER max_string_size;

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:

 CREATE TABLE customers (
  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:

INSERT INTO customers (id, name, email)
VALUES (1, 'Jordan Smith', 'j.smith@example.com');

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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list