SQL Oracle Insert Into NVarchar2(4000) 不允许 4000 个字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25452666/
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
Oracle Insert Into NVarchar2(4000) does not allow 4000 characters?
提问by Bruce Pullum
I have a table with a field datatype of NVarchar2(4000) I am moving data from a SQL Server to an Oracle Server. The SQL Server datatype is also nvarchar(4000). I have checked the MAX Size of this field on the SQL Server side, and the MAX is 3996, which is 4 characters short of the 4000 limit.
我有一个字段数据类型为 NVarchar2(4000) 的表，我正在将数据从 SQL Server 移动到 Oracle Server。SQL Server 数据类型也是 nvarchar(4000)。我在SQL Server端查过这个字段的MAX Size，MAX是3996，比4000的限制少了4个字符。
When I try to insert this data into Oracle, I get an error "LONG" due to the size.
当我尝试将这些数据插入 Oracle 时，由于大小的原因，我收到错误“LONG”。
What is going on here, will the Oracle NVarchar2(4000) not allow 4000 characters? If not, what is the limit, or how can I get around this?
这是怎么回事，Oracle NVarchar2(4000) 不允许 4000 个字符吗？如果没有，限制是什么，或者我如何解决这个问题？
采纳答案by Rahul Tripathi
There is a limit of 4000 bytes not 4000 characters. So NVARCHAR2(4000) with an AL16UTF16 national character set would occupy the maximum 4000 bytes.
有 4000 个字节而不是 4000 个字符的限制。因此，带有 AL16UTF16 国家字符集的 NVARCHAR2(4000) 将占用最大 4000 个字节。
From the oracle docs of MAX_STRING SIZE:
Tables with virtual columns will be updated with new data type metadata for virtual columns of VARCHAR2(4000), 4000-byte NVARCHAR2, or RAW(2000) type.
具有虚拟列的表将使用新的数据类型元数据更新为 VARCHAR2(4000)、4000 字节 NVARCHAR2 或 RAW(2000) 类型的虚拟列。
Solution:-
解决方案：-
Also if you want to store 4000 characters then I would recommend you to use CLOB
另外，如果您想存储 4000 个字符，那么我建议您使用CLOB
A CLOB (Character Large Object) is an Oracle data type that can hold up to 4 GB of data. CLOBs are handy for storing text.
CLOB（字符大对象）是一种 Oracle 数据类型，最多可容纳 4 GB 的数据。CLOB 可以方便地存储文本。
You may try like this to change column data type to CLOB:
您可以尝试像这样将列数据类型更改为 CLOB：
ALTER TABLE table_name
ADD (tmpcolumn CLOB);
UPDATE table_name SET tmpcolumn =currentnvarcharcolumn;
COMMIT;
ALTER TABLE table_name DROP COLUMN currentnvarcharcolumn;
ALTER TABLE table_name
RENAME COLUMN tmpcolumn TO whatevernameyouwant;
回答by Justin Cave
First, as others have pointed out, unless you're using 12.1, both varchar2and nvarchar2data types are limited in SQL to 4000 bytes. In PL/SQL, they're limited to 32767. In 12.1, you can increase the SQL limit to 32767 using the MAX_STRING_SIZEparameter.
首先，正如其他人指出的那样，除非您使用 12.1，否则SQL 中的varchar2和nvarchar2数据类型都限制为 4000 字节。在 PL/SQL 中，它们被限制为 32767。在 12.1 中，您可以使用MAX_STRING_SIZE参数将 SQL 限制增加到 32767 。
Second, unless you are working with a legacy database that uses a non-Unicode character set that cannot be upgraded to use a Unicode character set, you would want to avoid nvarchar2and nchardata types in Oracle. In SQL Server, you use nvarcharwhen you want to store Unicode data. In Oracle, the preference is to use varchar2in a database whose character set supports Unicode (generally AL32UTF8) when you want to store Unicode data.
其次，除非您使用的是无法升级为使用 Unicode 字符集的非 Unicode 字符集的旧数据库，否则您可能希望避免使用 Oracle 中的nvarchar2和nchar数据类型。在 SQL Server 中，nvarchar当您要存储 Unicode 数据时使用。在 Oracle 中，当您要存储 Unicode 数据时，首选是varchar2在字符集支持 Unicode（一般为AL32UTF8）的数据库中使用。
If you store Unicode data in an Oracle NVARCHAR2column, the national character set will be used-- this is almost certainly AL16UTF16which means that every character requires at least 2 bytes of storage. A NVARCHAR2(4000), therefore, probably can't store more than 2000 characters. If you use a VARCHAR2column, on the other hand, you can use a variable width Unicode character set (AL32UTF8) in which case English characters generally require just 1 byte, most European characters require 2 bytes, and most Asian characters require 3 bytes (this is, of course, just a generalization). That is generally going to allow you to store substantially more data in a VARCHAR2column.
如果将 Unicode 数据存储在 OracleNVARCHAR2列中，将使用国家字符集——这几乎可以肯定AL16UTF16，这意味着每个字符至少需要 2 个字节的存储空间。NVARCHAR2(4000)因此，A可能无法存储超过 2000 个字符。VARCHAR2另一方面，如果使用列，则可以使用可变宽度的 Unicode 字符集 ( AL32UTF8)，在这种情况下，英文字符通常只需要 1 个字节，大多数欧洲字符需要 2 个字节，大多数亚洲字符需要 3 个字节（这是，当然，只是一个概括）。这通常允许您在一VARCHAR2列中存储更多的数据。
If you do need to store more than 4000 bytes of data and you're using Oracle 11.2 or later, you'd have to use a LOBdata type (CLOBor NCLOB).
如果您确实需要存储超过 4000 字节的数据，并且您使用的是 Oracle 11.2 或更高版本，则必须使用LOB数据类型（CLOB或NCLOB）。
回答by Ben
As per the documentationthough the width refers to the number of characters there's still a 4,000 byte limit:
根据文档，尽管宽度是指字符数，但仍有 4,000 字节的限制：
Width specifications of character data type NVARCHAR2 refer to the number of characters. The maximum column size allowed is 4000 bytes.
字符数据类型 NVARCHAR2 的宽度规范是指字符数。允许的最大列大小为 4000 字节。
You probably have 4 multi-byte characters.
您可能有 4 个多字节字符。
