Oracle Varchar2 Datatype

In this Oracle tutorial, we will learn about the Varchar2 datatype in the oracle database. Also, we will demonstrate how to use it to create varchar2 columns for a table.

Introduction to Oracle Varchar2 Datatype

The character values are stored using the VarChar2 data type. Because it is a variable-length data type, we can alter the character variable’s size during execution.

The storage capacity of a VARCHAR2 column is 1–4000 bytes. It implies storing up to 4000 characters in a VARCHAR2 column for a single-byte character set.

Oracle Varchar2 datatype Syntax

The varchar2 datatype in a database and its function have been understood up to this point. After that, we’ll learn how to utilize it to make varchar2 columns for tables.

To do this, we must understand the Oracle 21c varchar datatype syntax, which is provided below.

VARCHAR2(max_size BYTE)
VARCHAR2(max_size CHAR)

If you don’t specifically mention BYTE or CHAR after the max size, Oracle will use BYTE by default. In other words, a VARCHAR2(N) column can store characters that are up to N bytes long.

Note: If you save 5 characters in char(11), Oracle will only store 5 bytes as opposed to 11 like it would with VARCHAR and CHAR.

Read: Oracle Database Datatype

Oracle varchar datatype Examples

After discussing the syntax, we will now discuss the example of how to use the varchar datatype in oracle 21c.

Here we will discuss how to manually use the varchar datatype in the SQL developer tool.

As you can see in the screenshot we have mentioned the column name ’emp_name’ and provided the datatype in this example, we utilize the varchar2 datatype with size(20).

Now we will insert the values into a table.

INSERT INTO employees VALUES ('Chris');

Example:

Now we will create a table by statement and check how we can use the varchar2 datatype in the given customers table.

CREATE TABLE customers (customer_name VARCHAR2(20));

First, we created a table named customers with a VARCHAR2 column (customer_name). The length of the column is 20 bytes.

We will insert the characters and display the character values in the customer_name column.

INSERT INTO customers VALUES ('George');

Now we will use the select statement for showing the ‘customer_name’ column value

select * from customers;

Read: How to copy a table in Oracle

Oracle VARCHAR2 datatype max length

A character string with a maximum length of 4000 bytes can be stored in a VarChar data type. A byte is also saved in the memory for every character.

Conclusion

So, in this Oracle tutorial, we understood how to define and use the Oracle varchar2 Datatype. And we have also covered a few sample examples related to it.

You may also like to read the following Oracle tutorials.

• How to convert rows into columns in Oracle
• Nchar Datatype in Oracle Database
• NVARCHAR2 Datatype in Oracle Database
• How to add a foreign key in Oracle
• How to add a primary key in Oracle