Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle VARCHAR2

Oracle VARCHAR2

Summary: in this tutorial, you will learn about the Oracle VARCHAR2 data type and how to use it to define variable-length character string columns.

Introduction to Oracle VARCHAR2 data type

To store variable-length character strings, you use the Oracle VARCHAR2 data type. A VARCHAR2 column can store a value that ranges from 1 to 4000 bytes. It means that for a single-byte character set, you can store up to 4000 characters in a VARCHAR2 column.

When you create a table with a VARCHAR2 column, you must specify the maximum string length, either in bytes:

VARCHAR2(max_size BYTE)

or in characters

VARCHAR2(max_size CHAR)

By default, Oracle uses BYTE if you don’t explicitly specify BYTE or CHAR after the max_size. In other words, a VARCHAR2(N) column can hold up to N bytes of characters.

If you store a character string whose size exceeds the maximum size of of the VARCHAR2 column, Oracle issues an error.

For example, if you define a VARCHAR2 column with a maximum size is 20. In a single-byte character set, you can store up to 20 characters. If you store 21 characters or more, Oracle returns an error.

In addition, if you store 10 characters in a VARCHAR2(20) column, Oracle uses only 10 bytes for storage, not 20 bytes. Therefore, using VARCHAR2 data type helps you save spaces used by the table.

When comparing VARCHAR2 values, Oracle uses the non-padded comparison semantics.

Oracle VARCHAR2 max length

Since Oracle Database 12c, you can specify the maximum size of 32767 for the VARCHAR2 data type. Oracle uses the MAX_STRING_SIZE parameter for controlling the maximum size. If the MAX_STRING_SIZE is STANDARD, then the maximum size for VARCHAR2 is 4000 bytes. In case, the MAX_STRING_SIZE is EXTENDED, the size limit for VARCHAR2 is 32767.

To get the value of the MAX_STRING_SIZE parameter, you use the following query:

SELECT name, value FROM v$parameter WHERE name = 'max_string_size';
Oracle VARCHAR2 max size

Or use the following statement:

SHOW PARAMETER max_string_size;

Oracle VARCHAR2 examples

The following statement creates a new table named econtacts to store employees’ emergency contacts.

CREATE TABLE econtacts ( econtact_id NUMBER generated BY DEFAULT AS identity PRIMARY KEY, employee_id NUMBER NOT NULL, first_name VARCHAR2( 20 ) NOT NULL, last_name VARCHAR2( 20 ) NOT NULL, phone VARCHAR2( 12 ) NOT NULL, FOREIGN KEY( employee_id ) REFERENCES employees( employee_id ) ON DELETE CASCADE );

The econtacts table has three VARCHAR2 columns: first_name, last_name, and phone.

The following statement inserts a new row into the econtacts table:

INSERT INTO econtacts( employee_id, first_name, last_name, phone ) VALUES( 1, 'Branden', 'Wiley', '202-555-0193' );

It worked as expected because the input data does not exceed the maximum size of the VARCHAR2 column.

However, the following statement fails to insert:

INSERT INTO econtacts( employee_id, first_name, last_name, phone ) VALUES( 10, 'Pablo Diego Jose Francisco', 'Gray', '202-555-0195' );

Because the input first name exceeds the maximum length of the first_name column, Oracle issued the following error:

SQL Error: ORA-12899: value too large for column "OT"."ECONTACTS"."FIRST_NAME" (actual: 26, maximum: 20)

In this tutorial, you have learned about the Oracle VARCHAR2 and how to use the VARCHAR2 data type to define variable-length character string columns in a table.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle NCHAR
Next Oracle NVARCHAR2

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.