同VARCHAR(35)列更新时ORA-12899报错差异原因咨询

Great question—this boils down to a critical difference in how Oracle interprets the VARCHAR2(35) definition for each table: byte vs. character length semantics. Let's break this down step by step.

The Root Cause #

Your database uses AL32UTF8 (a multi-byte character set), where characters like ñ take up 2 bytes instead of 1. Here's what's happening:

• Your target string has 35 characters (confirmed via length('Andres Peñalver D1 Palmar Sani salt') = 35), but takes up 36 bytes (from lengthb() = 36) because of the ñ.
• Table t's colm1 column is defined using character-length semantics (VARCHAR2(35 CHAR)), which counts characters instead of bytes. Since your string has exactly 35 characters, the update succeeds.
• Table t2's colm1 column uses byte-length semantics (the default if not specified otherwise, VARCHAR2(35 BYTE)), which limits the column to 35 bytes. Your 36-byte string exceeds this limit, triggering ORA-12899.

How to Verify the Column Definitions #

Run this query to check the length semantics for both columns:

SELECT 
  table_name,
  column_name,
  data_type,
  char_length,
  char_used -- 'C' = CHAR semantics, 'B' = BYTE semantics
FROM user_tab_columns
WHERE table_name IN ('T', 'T2') 
  AND column_name = 'COLM1';

You’ll see char_used is 'C' for table t and 'B' for table t2.

Fixes to Resolve the Issue #

You have two main options:

1. Adjust the column to use character semantics (recommended for multi-byte character sets):

   ALTER TABLE t2 MODIFY colm1 VARCHAR2(35 CHAR);
   

   This lets the column accept up to 35 characters, regardless of their byte count.
2. Shorten the string to fit 35 bytes:
   Replace multi-byte characters with single-byte equivalents (e.g., change ñ to n) to reduce the total byte length to 35.

Why the Difference Exists #

This usually happens if:

• The tables were created with different NLS_LENGTH_SEMANTICS settings (one session used CHAR, the other BYTE).
• One table’s column was explicitly defined with CHAR (e.g., VARCHAR2(35 CHAR)), while the other used the default BYTE semantics.

内容的提问来源于stack exchange，提问作者nithin ravi