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.

employee table created in oracle
employee table created in oracle

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');
varchar2 datatype in oracle
varchar2 datatype in oracle

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;
display the value for customer_name column in oracle
display the value for customer_name column in oracle

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.