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.