SQL ORA-12899 尽管长度相同，但列的值太大

ORA-12899 value too large for column despite of same length

提问by Shitu

I am running the following query. But getting ORA-12899. Altough the length of string am trying to insert is 30.

我正在运行以下查询。但是得到 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)

回答by Alex Poole

You're seeing the difference between 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.

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

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 byteswill 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)

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.

这就是你所看到的。当您插入其他表中的值时，您将过滤值的长度，但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

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).

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

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:

您可以更改插入以过滤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.

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 mightwork, but with any substitution you are destroying information that might actually be important.

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

回答by Rahul Tripathi

Try to change your table like

尝试改变你的桌子

ALTER TABLE1 MODIFY column1 VARCHAR2(30 CHAR)

The error states that your column1 can store maximum of 30 characters and you are passing more than 30 characters in it which results in the error.

该错误指出您的 column1 最多可以存储 30 个字符，并且您在其中传递了 30 个以上的字符，这导致了错误。

You can also refer this article: Oracle Database - Bytes or Characters for VARCHAR2 and CHAR

您还可以参考这篇文章：Oracle 数据库 - VARCHAR2 和 CHAR 的字节或字符

回答by Gehan Fernando

ORA-12899

Often times, as our companies grow and evolve in response to an expansion in the form of client-base, staff, profit or markets, the data associated with this growth will also change. Data systems like Oracle have the innate ability to remain fairly flexible in regards to working with this variance in information. Still, even the most versatile database systems require maintenance and refining in the face of increased data traffic. This work is essential to accommodating any constraints on memory or necessary redefinitions of parameters. The ORA-12899 error is representative of an instance in which either an uptick in data or a user error is forcing Oracle to stall during its requested action.

THE PROBLEM

The ORA-12899 is an Oracle error that occurs when the value entered into the column string is too large. What this means is that an attempt was made by the user to update or insert a column with a value that is too wide for the destination column. The name of a particular column will be given and the actual width of the value, as well as the maximum width permitted for the column, will be associated with that. As mentioned, the value can be given in the form of characters. In the event that the width is reported in characters, this will mean that character length semantics are operating for the column. Otherwise the width will be reported in bytes. Essentially, this error results from trying to push through a value, or set of values, that exceed the specified maximum width of the column. So, how does a user correct this type of error?

THE SOLUTION

To begin, open up the OERR utility. The user will require the full ORA-12899 message to receive the proper feedback on the error. This will provide more information on the error and allow further investigation. Typically, the error can come from one of three sources. The first source is the SQL statements that have been generated. Checking source and destination column data types to discover whether they are compatible with current formats is the second source. Lastly, the user can look at the destination column width – where the value is being assigned – to make sure it is large enough to accommodate the maximum value that the user anticipates assigning. Let us now turn to an example that corrects ORA-12899. Suppose the user has created the following table:

CREATE TABLE Clients(
ClientID varchar2(9) PRIMARY KEY,
Client_Contact varchar2(40) NOT NULL,
Address varchar(20) NOT NULL,
Zip varchar2(5) NOT NULL,
Client_Number varchar2(11) NOT NULL)

The user then attempts to issue an INSERT VALUES statement that looks something like this:

INSERT INTO Clients VALUES(
727546345,
‘Roger Holcomb',
‘—Benjamin Road Syracuse',
‘-----‘, 05307623754)

The user might try to run the statement from here, but will receive the following error message:

Error starting at line 7 in command: INSERT INTO Clients VALUES(727546345, ‘Ricky Galorey', ‘18 Benjamin Road Syracuse', ‘13208', 05307623754) Error report: SQL Error: ORA-12899: value too large for column “ORGANIZATIONS”. “MARKET”. “ADDRESS”(actual: 25, maximum: 20) 12899. 00000 – “value too large for column %s (actual: %s, maximum: %s)”

This error statement indicates that the variable ‘Address' cannot hold more than twenty characters as that would exceed the width of the column parameter. When we look back at the address value ('18 Benjamin Road Syracuse') we can see that the total number of characters (25) exceeds the maximum number allowed for the width of the column. To correct this, the user can change the VARCHAR2 for address to an amount that can accommodate the typical address length that their company would input.

ORA-12899

很多时候，随着我们的公司随着客户群、员工、利润或市场形式的扩张而成长和发展，与这种增长相关的数据也会发生变化。像 Oracle 这样的数据系统天生就具有在处理这种信息差异方面保持相当灵活的能力。尽管如此，面对不断增加的数据流量，即使是最通用的数据库系统也需要维护和改进。这项工作对于适应内存限制或必要的参数重新定义至关重要。ORA-12899 错误代表一个实例，其中数据增加或用户错误迫使 Oracle 在其请求的操作期间停顿。

问题

ORA-12899 是一个 Oracle 错误，当输入到列字符串中的值太大时会发生该错误。这意味着用户尝试更新或插入值对于目标列来说太宽的列。将给出特定列的名称，并且值的实际宽度以及列允许的最大宽度将与其相关联。如前所述，该值可以以字符的形式给出。如果宽度以字符为单位报告，这将意味着字符长度语义正在为该列运行。否则宽度将以字节为单位报告。本质上，此错误是由于尝试推送超过指定的列最大宽度的值或值集所致。所以，

解决方案

首先，打开 OERR 实用程序。用户需要完整的 ORA-12899 消息才能收到有关错误的正确反馈。这将提供有关错误的更多信息并允许进一步调查。通常，错误可能来自三个来源之一。第一个来源是已生成的 SQL 语句。检查源和目标列数据类型以发现它们是否与当前格式兼容是第二个来源。最后，用户可以查看目标列的宽度 - 值被分配的位置 - 以确保它足够大以容纳用户预期分配的最大值。现在让我们来看一个纠正 ORA-12899 的例子。假设用户创建了下表：

CREATE TABLE Clients(
ClientID varchar2(9) PRIMARY KEY,
Client_Contact varchar2(40) NOT NULL,
Address varchar(20) NOT NULL,
Zip varchar2(5) NOT NULL,
Client_Number varchar2(11) NOT NULL)

然后，用户尝试发出如下所示的 INSERT VALUES 语句：

INSERT INTO Clients VALUES(
727546345,
‘Roger Holcomb',
‘—Benjamin Road Syracuse',
‘-----‘, 05307623754)

用户可能会尝试从这里运行语句，但会收到以下错误消息：

从命令第 7 行开始出错：INSERT INTO Clients VALUES(727546345, 'Ricky Galorey', '18 Benjamin Road Syracuse', '13208', 05307623754) 错误报告：SQL 错误：ORA-12899：列的值太大“ ”。“市场”。“地址”（实际：25，最大值：20）12899. 00000 – “列 %s 的值太大（实际：%s，最大值：%s）”

此错误语句表明变量“地址”不能容纳超过 20 个字符，因为这会超出列参数的宽度。当我们回顾地址值 ('18 Benjamin Road Syracuse') 时，我们可以看到字符总数 (25) 超过了列宽度所允许的最大数量。为了更正此问题，用户可以将地址的 VARCHAR2 更改为可以容纳其公司将输入的典型地址长度的数量。

The above answer original URL

以上答案原网址

回答by lakshmi tatavarty

Due to different NLS settings in the target table database it might require more bytes in the target. Try altering the table as alter Table1 modify column1 varchar2(30 char)

由于目标表数据库中的 NLS 设置不同，目标中可能需要更多字节。尝试将表更改为 alter Table1 modify column1 varchar2(30 char)