ORA-12899 means a text value is longer than its column allows. The message names the column and gives the actual and maximum lengths. A surprising case is text with accented or non-Latin characters: a VARCHAR2(10) column is measured in bytes by default, and such characters take two or more bytes in UTF-8, so a ten-character word can still be too long.
Reproduce the Error
Example:
create table demo_tags (tag varchar2(10));
insert into demo_tags values ('performance');
insert into demo_tags values ('Zürich-Bern');Output:
Table DEMO_TAGS created.
Error starting at line : 2
In command -
insert into demo_tags values ('performance')
Error at Command Line : 2 Column : 31
Error report -
SQL Error: ORA-12899: value too large for column "NIMBUS"."DEMO_TAGS"."TAG" (actual: 11,
maximum: 10)
Error starting at line : 3
In command -
insert into demo_tags values ('Zürich-Bern')
Error at Command Line : 3 Column : 31
Error report -
SQL Error: ORA-12899: value too large for column "NIMBUS"."DEMO_TAGS"."TAG" (actual: 12,
maximum: 10)performance has 11 characters. Zürich-Bern has 11 characters but 12 bytes, because ü takes two bytes.
Fix: Size the Column in Characters
Example:
select tag, length(tag) as chars, lengthb(tag) as bytes from (select 'Zürich-Bern' as tag from dual);
alter table demo_tags modify tag varchar2(20 char); -- size in characters, not bytes
insert into demo_tags values ('performance');
insert into demo_tags values ('Zürich-Bern');
select tag from demo_tags;Output:
TAG CHARS BYTES ______________ ________ ________ Zürich-Bern 11 12 Table DEMO_TAGS altered. 1 row inserted. 1 row inserted. TAG ______________ performance Zürich-Bern
LENGTH counts characters and LENGTHB bytes. VARCHAR2(20 CHAR) holds 20 characters whatever their size in bytes.
Other Ways
- Truncate on purpose with SUBSTR when only a prefix is needed.
- Check lengths before inserting, in the application or with a check constraint, and report a clear message.
- For bulk loads, DML error logging collects the rows that are too long instead of stopping the load.
Things to Know
- The session parameter NLS_LENGTH_SEMANTICS decides whether VARCHAR2(n) without BYTE or CHAR means bytes or characters.
- VARCHAR2 holds up to 4,000 bytes by default, or 32,767 with MAX_STRING_SIZE set to EXTENDED; longer text needs a CLOB.
Related Guides
- How to Log DML Errors in Oracle
- How to Fix ORA-01438: Value Larger Than Specified Precision in Oracle
Conclusion
ORA-12899 gives the column, the value's length, and the maximum. Widen the column, preferably in characters with the CHAR keyword, or shorten or reject the value before it reaches the table.
