Why Oracle VARCHAR2 Fails at 4000 Bytes and How to Fix It

Background

A transportation company's production database added an OceanBase→Oracle reverse incremental sync (backflow) link. During operation the sync failed with an error indicating that the DESCRIPTION column exceeded its maximum length.

Error message:

db:RENZY,table:T_RENZY_2508.ORA-12899:value too large for column "RENZY"."T_RENZY_2508"."DESCRIPTION" (actual:4805,maxmum:4000)

Version information

Source: OceanBase 4.2.1.9

Target: Oracle 19c

Investigation Process

Locate incr‑sync component logs

Check the error.log of the incr‑sync component. The log shows that OMS attempted to insert a row whose DESCRIPTION field length (4805 bytes) exceeds the target column definition.

[2025-07-09 19:00:59.015] [ERROR] ... INSERT ... DESCRIPTION=业务Begin启用,单据类型:Payment... ORA-12899: value too large for column "RENZY"."T_RENZY_2508"."DESCRIPTION" (actual: 4805, maximum: 4000)

Analyze source and target table structures

Source side OceanBase

Table definition:

CREATE TABLE "T_RENZY_2508" (
  "ID" VARCHAR2(36) NOT NULL,
  ...
  "DESCRIPTION" VARCHAR2(4000),
  PRIMARY KEY ("ID")
);

Length check:

# Character length
SELECT LENGTH(DESCRIPTION) FROM T_RENZY_2508 WHERE id='4f3d2e8f-edb9-404d-8144-efb071d7f848';
-- returns 2259
# Byte length
SELECT LENGTHB(DESCRIPTION) FROM T_RENZY_2508 WHERE id='4f3d2e8f-edb9-404d-8144-efb071d7f848';
-- returns 4805

Target side Oracle

CREATE TABLE "RENZY"."T_RENZY_2508" (
  "ID" VARCHAR2(36) NOT NULL,
  ...
  "DESCRIPTION" VARCHAR2(4000),
  PRIMARY KEY ("ID")
);

Oracle’s NLS_LENGTH_SEMANTICS determines whether VARCHAR2(4000) is interpreted as 4000 CHAR or 4000 BYTE. In this environment it is set to BYTE, so the actual byte limit is 4000.

OceanBase treats DESCRIPTION as VARCHAR2(4000 CHAR).

Oracle treats it as VARCHAR2(4000 BYTE).

This explains why OceanBase can store 4805 bytes while Oracle rejects the insert.

Attempt to align Oracle length semantics with OceanBase

Modify the column definition:

ALTER TABLE T_RENZY_2508 MODIFY (DESCRIPTION VARCHAR2(4000 CHAR));

After restarting the OMS component the error persisted.

Why the modification still fails?

Manual data insertion reproduces the error

Creating a test table in Oracle and inserting a ~4000‑character literal triggers: ORA-01704: string literal too long Oracle limits the size of a string literal in an INSERT statement to 4000 bytes, regardless of column definition.

Error cause

Oracle 19c enforces a hard 4000‑byte limit for VARCHAR2 literals.

The SQL parser raises the error before the row reaches the column definition.

Conclusion

Root cause

The target Oracle database’s VARCHAR2 maximum is 4000 BYTE because NLS_LENGTH_SEMANTICS is set to BYTE.

OceanBase uses CHAR semantics, allowing more than 4000 bytes when characters are multibyte.

Workarounds

Method 1 – Extend string size

Set MAX_STRING_SIZE = EXTENDED (requires SPFILE change and database restart) so VARCHAR2 can hold up to 32767 bytes.

The operation is irreversible and should be used with caution.

ALTER SYSTEM SET MAX_STRING_SIZE = EXTENDED SCOPE = SPFILE;

Restart the database, run @?/rdbms/admin/utl32k.sql, then insert using a PL/SQL variable:

DECLARE
  v_desc VARCHAR2(32767);
BEGIN
  v_desc := '...first part...'
           || '...second part...';
  INSERT INTO RENZY.tmp_renzy20250709 (DESCRIPTION) VALUES (v_desc);
  COMMIT;
END;
/

Method 2 – Use CLOB

Change the column type to CLOB and insert via PL/SQL, bypassing the 4000‑byte literal limit.

CREATE TABLE tmp_renzy20250709 (
  DESCRIPTION CLOB
);

DECLARE
  v_clob CLOB := EMPTY_CLOB();
BEGIN
  v_clob := v_clob || '...chunk1...';
  v_clob := v_clob || '...chunk2...';
  INSERT INTO RENZY.tmp_renzy20250709 (DESCRIPTION) VALUES (v_clob);
  COMMIT;
END;
/

Complete resolution

Ensure NLS_LENGTH_SEMANTICS is consistent (prefer BYTE) on both sides.

When large text must be stored, use CLOB or enable MAX_STRING_SIZE=EXTENDED.

Reference

Oracle 19c VARCHAR2 maximum length

The maximum length of VARCHAR2 depends on the NLS_LENGTH_SEMANTICS setting and the MAX_STRING_SIZE parameter:

Oracle 11g and earlier: 4000 BYTE (default) or 4000 CHAR (but still limited to 4000 BYTE).

Oracle 12c+ introduces MAX_STRING_SIZE: STANDARD – same 4000 BYTE limit. EXTENDED – up to 32767 BYTE/CHAR, requires database restart and is irreversible.

Note: Regardless of version, the actual byte count of a VARCHAR2 value must not exceed the configured maximum.