ORACLE SQL – Difference between VARCHAR2(5 byte) and VARCHAR2(5)

Database version: 10g Release2

VARCHAR2(5 byte) -- You can store up to 5 bytes

VARCHAR2(5) -- You can store up to 5 characters

But we need to be careful when we try to store multi-byte characters like Japanese characters. Here each character needs more than one byte to store in the database.

When we use VARCHAR2(5 byte) definition and if the Japanese character takes 3 bytes then we can save only one character to that column.

When we use VARCHAR2(5) definition and if the Japanese character takes 3 bytes still we can save only one character to that column.

CREATE TABLE test_varchar(
colA VARCHAR2(5),
colB VARCHAR2(5 byte));
Table created

INSERT INTO test_varchar
VALUES ('aaaaa','bbbbb');

1 row inserted

INSERT INTO test_varchar
VALUES ('aaaaa','気木機能性');

ORA-12899: value too large for column "TEST_VARCHAR"."COLB" (actual: 15, maximum: 5)

INSERT INTO test_varchar
VALUES ('気木機能性', 'aaaaa');

ORA-12899: value too large for column "TEST_VARCHAR"."COLA" (actual: 15, maximum: 5)

When we used the “VARCHAR2(5)” definition we receive the “Value too large” error because by default, the character datatypes (CHAR and VARCHAR2) are specified in bytes, not characters. Hence, the specification VARCHAR2(5)in a table definition allows 5 bytes for storing character data.

How we can overcome this issue?

We can overcome this problem by switching to character semantics when defining the column size.

NLS_LENGTH_SEMANTICS enables you to create CHAR, VARCHAR2, and LONG columns using either byte or character length semantics. NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. Existing columns are not affected.

We can set the NLS_LENGTH_SEMANTICS as CHAR.

Now consider the following example.

ALTER SESSION
SET NLS_LENGTH_SEMANTICS = 'CHAR';

CREATE TABLE test_varchar2(
colC VARCHAR2(5),
colD VARCHAR2(5 byte));
Table created

INSERT INTO test_varchar2
VALUES ('aaaaa','bbbbb');

1 row inserted

INSERT INTO test_varchar2
VALUES ('気木機能性', 'aaaaa');

1 row inserted

Now we can successfully save 5 multi-byte characters into the colC in test_varchar2 table.