尽管长度相同，ORA-12899的值对于列还是太大 [英] ORA-12899 value too large for column despite of same length

问题描述

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

I am running the following query. But getting ORA-12899. Altough the length of string am trying to insert is 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)

推荐答案

您正在看到

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

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

If your session is using byte semantics then the column in your table will default to that:

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

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

That is the same as explicitly doing:

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

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

If your source data is five characters but includes any multibyte characters then the number of bytes will exceed five:

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个字节，因此这些字符之一是多字节.

Which is what you're seeing. When you insert the values from your other table you are filter on the length of the values, but length() counts characters rather than bytes. There is a lengthb() function that does count bytes. If you check the byte length of the 30-character value you're selecting you'll see it is in fact 31 bytes, so one of those characters is multibyte.

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，即

From the dump() values you can see that after Testing (54,65,73,74,69,6e,67) and before the space and dash (20,2d) you have c2,a0, which is the UTF-8 multibyte non-breaking space character. (You often see that, along with curly quotes and other non-ASCII-range characters, in text that has been copied from,. say, a Word document).

您可以更改插入内容以在LENGTHB(column1)=30上进行过滤(这将排除当前找到的行)，或者将列定义更改为30个字符而不是30个字节:

You can either change your insert to filter on LENGTHB(column1)=30 (which will exclude the row you currently find), or change your column definition to 30 characters instead of 30 bytes:

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.

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

Or replace any unexpected multibyte characters with single-byte equivalents, if that's possible and makes sense for your data; in this case a normal space might work, but with any substitution you are destroying information that might actually be important.

这篇关于尽管长度相同，ORA-12899的值对于列还是太大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！