Oracle 无法将数据插入到 varchar2(4000 char) 列中 [英] Oracle not able to insert data into varchar2(4000 char) column

问题描述

我使用 Oracle 12c.我的数据库中有下表.

I use Oracle 12c. I have below table in my DB.

CREATE TABLE TEST_T (COL VARCHAR2(4000 CHAR));

我需要在这个表中插入多字节字符.字符为 3 字节字符.
我只能在表中插入 1333 个(最多 3999 个字节)字符.我的期望是最多插入 1500 个多字节字符，但我得到 ORA - 01461.
我不想将数据类型更改为 CLOB 或 LONG.有什么方法可以使用 VARCHAR2(4000 CHAR) 来实现这一点.

I need insert multibyte characters in this table. The character is 3 byte character.
I am able to insert only 1333 (upto 3999 bytes) characters in table. My expectation is to insert upto 1500 multibyte characters but I get ORA - 01461.
I don't want to change data type to CLOB or LONG. Is there any way to use VARCHAR2(4000 CHAR) to achieve this.

下面是代码，

SET SERVEROUTPUT ON
DECLARE
  LV_VAR CHAR(1):='プ';     -- 3 byte character
  LV_STR VARCHAR2(32000) := '';
BEGIN
  FOR I IN 1..1500
  LOOP
    LV_STR := LV_STR||LV_VAR;
  END LOOP;
--
  INSERT INTO TEST_T VALUES (LV_STR);
END;
/



Error report -
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 11
01461. 00000 -  "can bind a LONG value only for insert into a LONG column"
*Cause:    
*Action:

推荐答案

问题是 4000 字节的限制是硬限制，不管数据类型是否定义为 VARCHAR2(4000 CHAR)、VARCHAR2(4000 BYTE) 或 NVARCHAR2(4000).这意味着多字节字符总是有可能溢出最大大小的非 CLOB 文本列.

The problem is that the 4000 byte limit is a hard limit, regardless of whether the datatype is defined as VARCHAR2(4000 CHAR), VARCHAR2(4000 BYTE), or NVARCHAR2(4000). This means that multibyte characters will always have the chance of overflowing a max-size non-CLOB text column.

Oracle 的 数据类型限制表显示每个VARCHAR2变体最多可容纳4000个字节.而这正是您遇到的问题.

Oracle's table of Datatype Limits shows each of the VARCHAR2 variants as holding a max of 4000 bytes. And this is precisely the problem you have encountered.

您可以选择将 Oracle 12c 数据库中 VARCHAR2 的最大大小增加到 32k.

You do have the option of increasing the max size for VARCHAR2 in your Oracle 12c database to 32k.

操作方法如下:MAX_STRING_SIZE 文档

如果不仔细考虑，就不能这样做:一旦您将数据库更改为使用扩展的 VARCHAR2 字符串，您将无法返回.然而，如果您的数据库完全是您自己的，并且您喜欢拥有 32K 字符串的想法，那么此功能是专门为解决您的情况而创建的.

This is not something to be done without careful consideration: once you change your database to use extended VARCHAR2 strings you cannot go back. Nevertheless, if your database is all your own and you like the idea of having 32K strings, then this feature was created specifically to address your situation.

请仔细阅读可插拔数据库、容器数据库的详细信息，因为它们需要不同的升级技术.这是一个贯穿整个数据库的更改，因此您需要正确处理.

Be careful to read the details of pluggable databases, container databases as they require different upgrade techniques. This is a change that cuts across the entire database so you want to get it right.

这篇关于Oracle 无法将数据插入到 varchar2(4000 char) 列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！