尽管长度相同，ORA-12899的值对于列还是太大

Question

我正在运行以下查询。但是得到ORA-12899。试图插入的字符串的总长度为30。

INSERT INTO TABLE1 SELECT * FROM temp_Table1 where LENGTH(column1)=30;

SQL Error: ORA-12899:value too large for column "TABLE1"."column1" (actual: 31, maximum: 30)


select column1 from temp_Table1 where LENGTH(column1)=30;

Testing聽 - HLC/TC Design Corre

Desc temp_Table1

column1 VARCHAR2(30)

Desc Table1

column1 VARCHAR2(30)

Answer 1

您会看到字符和字节长度语义之间的区别：

您必须为VARCHAR2列指定最大长度。尽管允许存储的实际字符串为零长度字符串（’‘），但该最大值必须至少为1个字节。您可以使用CHAR限定符，例如VARCHAR2（10
CHAR），以字符（而不是字节）为单位提供最大长度。从技术上讲，字符是数据库字符集的代码点。您可以使用BYTE限定符，例如VARCHAR2（10
BYTE），以字节为单位明确给出最大长度。如果在创建具有此列或属性的数据库对象时，在列或属性定义中未包含任何明确的限定词，则长度语义由创建对象的会话的NLS_LENGTH_SEMANTICS参数的值确定。

如果您的会话使用字节语义，那么表中的列将默认为：

select value from nls_session_parameters where parameter = 'NLS_LENGTH_SEMANTICS';

VALUE                                  
----------------------------------------
BYTE

create table t42(text varchar2(5));

Table T42 created.

select char_used from user_tab_columns where table_name = 'T42' and column_name = 'TEXT';

C
-
B

这与显式执行的操作相同：

create table t42(text varchar2(5 byte));

如果您的源数据是五个字符但包含任何多字节字符，则 字节 数将超过五个：

insert into t42 (text) values ('Hello');

1 row inserted.

insert into t42 (text) values ('Se帽or');

SQL Error: ORA-12899: value too large for column "SCHEMA"."T42"."TEXT" (actual: 6, maximum: 5)

这就是您所看到的。当您从其他表中插入值时，您会根据值的长度进行过滤，但是会length()计算字符而不是字节。有一个lengthb()函数可以计算字节数。如果检查所选的30个字符的值的字节长度，您会发现它实际上是31个字节，因此这些字符之一是多字节。

with t42 (text) as (
  select 'Hello' from dual
  union all select 'Se帽or' from dual
  union all select 'Testing聽 - HLC/TC Design Corre' from dual
)
select text, length(text) as chars, lengthb(text) as bytes, dump(text, 16) as hex
from t42;

TEXT                            CHARS BYTES HEX                                                                                                      
------------------------------- ----- ----- ----------------------------------------------------------------------------------------------------------
Hello                               5     5 Typ=1 Len=5: 48,65,6c,6c,6f                                                                               
Se帽or                               5     6 Typ=1 Len=6: 53,65,c3,b1,6f,72                                                                            
Testing聽 - HLC/TC Design Corre     30    31 Typ=1 Len=31: 54,65,73,74,69,6e,67,c2,a0,20,2d,20,48,4c,43,2f,54,43,20,44,65,73,69,67,6e,20,43,6f,72,72,65

从这些dump()值中，您可以看到在Testing（54,65,73,74,69,6e,67）之后，在空格和破折号（20,2d）之前c2,a0，这是UTF-8多字节不间断空格字符。（您经常会在从（例如）Word文档复制的文本中看到，连同弯引号和其他非ASCII范围的字符）。

您可以更改插入内容以进行过滤LENGTHB(column1)=30（将排除当前找到的行），也可以将列定义更改为30个字符而不是30个字节：

drop table t42;

Table T42 dropped.

create table t42(text varchar2(5 char));

Table T42 created.

select char_used from user_tab_columns where table_name = 'T42' and column_name = 'TEXT';

C
-
C

insert into t42 (text) values ('Hello');

1 row inserted.

insert into t42 (text) values ('Se帽or');

1 row inserted.

或者，如果有可能并且对您的数据有意义，请用单字节等效项替换任何意外的多字节字符。在这种情况下，正常的空间 可能会
起作用，但是如果进行任何替换，您将破坏实际上可能很重要的信息。