oracle 为什么我会收到 ORA-01401：插入的值对于列来说太大了 - 当我不插入时？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19537234/
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
Why am I getting ORA-01401: inserted value too large for column - when I'm not inserting?
提问by Jon Spokes
Here is some SQL to set up with a very simple table.
这是一些使用非常简单的表设置的 SQL。
CREATE TABLE CC_TEST2
("CURRENCYID" NUMBER NOT NULL ENABLE,
"NAME" NVARCHAR2(255)) ;
insert into CC_TEST2 (select 1,'Testing issue'from dual);
commit;
Then this recreates the issue
然后这重新创建了问题
SELECT (step.Name ||
'Commentary of 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890
1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890
1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890
1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890
1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890
1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890
1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 12')
as thing FROM CC_TEST2 step
Any ideas?
有任何想法吗？
I think it's something odd about nVarchar2? If I change the column type to varChar2 then it's OK. Sadly I can't change the column type of the actual production database where I'm getting the issue
我认为 nVarchar2 有点奇怪？如果我将列类型更改为 varChar2，那么就可以了。遗憾的是，我无法更改出现问题的实际生产数据库的列类型
采纳答案by user2342436
If "NAME" NVARCHAR2(255) is changed to "NAME" VARCHAR2(255) (i.e by using varchar2) you won't get any issue. You can test the same at http://sqlfiddle.com/#!4/cefd8/2
如果将“NAME” NVARCHAR2(255) 更改为“NAME” VARCHAR2(255)（即使用 varchar2），您将不会遇到任何问题。您可以在http://sqlfiddle.com/#!4/cefd8/2测试相同
回答by Colin 't Hart
There seems to be some strangeness with NVARCHAR2 and string concatenation.
NVARCHAR2 和字符串连接似乎有些奇怪。
See http://sqlfiddle.com/#!4/936c4/2
见http://sqlfiddle.com/#!4/936c4/2
My understanding based on running the various statements in the SQL Fiddle is that the string constant on the right hand side of the concatenation operator || is also treated as an NVARCHAR2, and can be at most 1000 characters.
我基于运行 SQL Fiddle 中的各种语句的理解是连接运算符右侧的字符串常量 || 也被视为 NVARCHAR2，最多可包含 1000 个字符。
回答by SriniV
NCHAR and NVARCHAR2 Datatypes
NCHAR 和 NVARCHAR2 数据类型
NCHARand NVARCHAR2are Unicode datatypes that store Unicode character data. They are also called as Native data types.
NCHAR和NVARCHAR2是存储 Unicode 字符数据的 Unicode 数据类型。它们也称为本机数据类型。
The NVARCHAR2datatype stores variable length character strings.
该NVARCHAR2数据类型存储可变长度字符串。
When you create a table with an NVARCHAR2column, the maximum size specified is always in character length semantics. Character length semantics is the default and only length semantics for NVARCHAR2.
创建带有NVARCHAR2列的表时，指定的最大大小始终符合字符长度语义。字符长度语义是NVARCHAR2.
For example, if national character set is UTF8, then the following statement defines the maximum byte length of 90 bytes:
例如，如果国家字符集是UTF8，那么下面的语句定义了 90 个字节的最大字节长度：
CREATE TABLE tab1 (col1 NCHAR(30));
The maximum length of an NVARCHAR2column is 4000 bytes. It can hold up to 4000 characters. The actual data is subject to the maximum byte limit of 4000. The two size constraints must be satisfied simultaneously at run time.
一NVARCHAR2列的最大长度为 4000 字节。它最多可容纳 4000 个字符。实际数据受最大字节数限制为 4000。在运行时必须同时满足这两个大小限制。
It all depends on your character set and length semantics.
这一切都取决于您的字符集和长度语义。
One other important thing to remember is that the upper bound of the number of bytes stored in a VARCHAR2 is 4,000. However, even if you specify VARCHAR2(4000 CHAR), you may not be able to fit 4,000 characters into that field. In fact, you may be able to fit as few as 1,000 characters in that field if all of the characters take 4 bytes to be represented in your chosen character set!
要记住的另一件重要事情是存储在 VARCHAR2 中的字节数的上限是 4,000。但是，即使您指定 VARCHAR2(4000 CHAR)，您也可能无法在该字段中放入 4,000 个字符。事实上，如果所有字符都需要 4 个字节才能在您选择的字符集中表示，那么您可能能够在该字段中容纳少至 1,000 个字符！
This holds good for Native datatype also.
这也适用于 Native 数据类型。
Solution:
解决方案：
Use CHARinstead of BYTESas the length semantics for VARCHAR2 or CHAR.
使用CHAR代替BYTES作为 VARCHAR2 或 CHAR 的长度语义。
The reason is that a 20-character string in a single-byte character set is 20 bytes long and will absolutely fit in a VARCHAR2(20). However a 20-character field could be as long as 80 bytes in a multibyte character set, and 20 Unicode characters may well not fit in 20 bytes.
原因是单字节字符集中的 20 个字符的字符串有 20 个字节长，绝对适合 VARCHAR2(20)。然而，一个 20 个字符的字段在多字节字符集中可能长达 80 个字节，而 20 个 Unicode 字符可能不适合 20 个字节。
