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)Code language: SQL (Structured Query Language) (sql)

or in characters

VARCHAR2(max_size CHAR)Code language: SQL (Structured Query Language) (sql)

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 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';
Code language: SQL (Structured Query Language) (sql)

Or use the following statement:

SHOW PARAMETER max_string_size;
Code language: SQL (Structured Query Language) (sql)

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
);Code language: SQL (Structured Query Language) (sql)

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'
    );
Code language: SQL (Structured Query Language) (sql)

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'
    );
Code language: SQL (Structured Query Language) (sql)

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)
Code language: SQL (Structured Query Language) (sql)

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.