oracle ORA-12899: 列的值太大
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27576972/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-12899: value too large for column
提问by raju
I am getting data from erp systems in the form of feeds ,in particular one column length in feed is 15 only.
我正在以提要的形式从 erp 系统获取数据，特别是提要中的一列长度仅为 15。
In target table also corresponded column also length is varchar2(15)but when I am trying to load same into db it showing error like:
在目标表中也对应的列也是 length ，varchar2(15)但是当我尝试将其加载到 db 时，它显示如下错误：
ORA-12899: value too large for column emp_name (actual: 16, maximum: 15)
ORA-12899: 列 emp_name 的值太大（实际：16，最大值：15）
I cant increase the column length since it is base table in the production.
我无法增加列长度，因为它是生产中的基表。
回答by Frank Schmitt
The usual reason for problems like this are non-ASCII characters that can be represented with one byte in the original database but require two (or more) bytes in the target database (due to different NLS settings).
此类问题的常见原因是非 ASCII 字符可以在原始数据库中用一个字节表示，但在目标数据库中需要两个（或更多）字节（由于 NLS 设置不同）。
To ensure your target column is large enough for 15 characters, you can modify it:
为确保您的目标列足够容纳 15 个字符，您可以修改它：
ALTER TABLE table_name MODIFY column_name VARCHAR2(15 CHAR)
(note the 15 CHAR- you can also use BYTE; if neither is present, the database uses the NLS_LENGTH_SEMANTICS setting as a default).
（注意15 CHAR- 您也可以使用BYTE; 如果两者都不存在，则数据库使用 NLS_LENGTH_SEMANTICS 设置作为默认设置）。
To check which values are larger than 15 bytes, you can
要检查哪些值大于 15 个字节，您可以
- create a staging table in the target database with the column length set to
15 CHAR - insert the data from the source table into the staging table
find the offending rows with
SELECT * FROM staging WHERE lengthb(mycol) > 15
- 在目标数据库中创建一个临时表，列长度设置为
15 CHAR - 将源表中的数据插入到临时表中
找到有问题的行
SELECT * FROM staging WHERE lengthb(mycol) > 15
(note the use of LENGTHBas apposed to LENGTH- the former returns the length in bytes, whereas the latter returns the length in characters)
（注意使用LENGTHBas apposed to LENGTH- 前者以字节为单位返回长度，而后者以字符为单位返回长度）
回答by Ankireddy Polu
have a look into this blog, the problem resolved for me by changing the column datatype from varchar(100) to varchar(100 char). in my case the data contains some umlaut characters.
看看这个博客，通过将列数据类型从 varchar(100) 更改为 varchar(100 char) 为我解决了问题。在我的情况下，数据包含一些变音字符。
http://gerardnico.com/wiki/database/oracle/byte_or_character
http://gerardnico.com/wiki/database/oracle/byte_or_character
回答by jim mcnamara
I found AL32UTF8 as the only valid setting. This varies from standard UTF8 with a few character having supplementary bytes, i.e, the characters are about 99% the same. I am guessing you have character conversion problems going on. In other words the data in table1 was written using one charset, and the new table has a slightly different charset.
我发现 AL32UTF8 是唯一有效的设置。这与标准 UTF8 不同，有几个字符具有补充字节，即字符大约 99% 相同。我猜你有字符转换问题。换句话说，table1 中的数据是使用一个字符集写入的，而新表的字符集略有不同。
If this is true, you have to find the source of the oddball charset. Because this will continue to happen.
如果这是真的，您必须找到奇怪字符集的来源。因为这将继续发生。
回答by Swati Mishra
Solution to:
解决办法：
ORA-12899: VALUE TOO LARGE FOR COLUMN(ACTUAL,MAXIMUM)
ORA-12899：列的值太大（实际，最大值）
If you are facing problem while updating a column size of a table which already has data more than the new length below is the simple script that would work definitely.
如果您在更新表的列大小时遇到问题，该表的数据已经超过下面的新长度，那么这个简单的脚本肯定会起作用。
ALTER TABLE TABLE_NAME ADD (NEW_COLUMN_NAME DATATYPE(DATASIZE));
UPDATE TABLE_NAME SET NEW_COLUMN_NAME = SUBSTR(OLD_COLUMN_NAME , 1, NEW_LENGTH);
ALTER TABLE TABLE_NAME DROP COLUMN OLD_COLUMN_NAME ;
ALTER TABLE TABLE_NAME RENAME COLUMN NEW_COLUMN_NAME TO OLD_COLUMN_NAME;
Meaning of the query:
查询的含义：
ALTER TABLE TABLE_NAME ADD (NEW_COLUMN_NAME DATATYPE(DATASIZE));
It would just create a new column of the required new length in your existing table.
它只会在现有表中创建一个具有所需新长度的新列。
UPDATE TABLE_NAME SET NEW_COLUMN_NAME = SUBSTR(OLD_COLUMN_NAME , 1, NEW_LENGTH);
It will discard all the values after the new length value from old column values and set the trimmed values into the new column name.
它将丢弃旧列值中新长度值之后的所有值，并将修剪后的值设置为新列名。
ALTER TABLE TABLE_NAME DROP COLUMN OLD_COLUMN_NAME ;
It will remove the old column name as its absurd now and we have copied all the information into the new column.
它将删除旧的列名，因为它现在很荒谬，我们已将所有信息复制到新列中。
ALTER TABLE TABLE_NAME RENAME COLUMN NEW_COLUMN_NAME TO OLD_COLUMN_NAME;
Renaming the new column name to the old column name would help you regain the original table structure except for the new column size as you wished.
将新列名重命名为旧列名将帮助您重新获得原始表结构，除了您希望的新列大小。
