我在运行以下查询，但是出现了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)
您正在了解 字符长度语义和字节长度语义之间的区别：
您必须为 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.
如果可能并对您的数据有意义，请替换任何意外的多字节字符为单字节等效字符；在这种情况下，普通空格 可能 有效，但是使用任何替换都会破坏可能实际上很重要的信息。
尝试将您的表格更改为
ALTER TABLE1 MODIFY column1 VARCHAR2(30 CHAR)
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.