sql - 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

您会看到 character and byte length semantics 之间的区别:

You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. A character is technically a code point of the database character set. You can use the BYTE qualifier, for example VARCHAR2(10 BYTE), to explicitly give the maximum length in bytes. If no explicit qualifier is included in a column or attribute definition when a database object with this column or attribute is created, then the length semantics are determined by the value of the NLS_LENGTH_SEMANTICS parameter of the session creating the object.

如果您的 session 使用字节语义，则表中的列将默认为:

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，即 the UTF-8 multibyte non-breaking space character。 (您经常在从 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.

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