proc中插入VARCHAR2字段报ORA-01461: can bind a LONG value only for insert into a LONG column

最近做了一个天信达货运接口项目，遇到了这么奇怪的问题。

背景：

使用proc编写的应用程序将记录插入数据库。表中有三个VARCHAR2(4000)字段。注:Oracle 9i的库。

问题：

执行时提示：ORA-01461: can bind a LONG value only for insert into a LONG column

不能插入记录，但使用PLSQL 手动执行同一SQL的Developer或SQLPLUS没有问题。

然后换一个10g的库，用proc正确插入。

9i的库对proc插入有什么特殊限制吗？

解决过程：

1. 查询OERR对这个问题的说明:

有了这个问题，OERR首先看到了1461的错误，没有任何解释。

2. 查询MOS对这个问题的说明:

然后检查MOS，Workarounds for bug 1400539: GETTING ORA-1461 INSERTING INTO A VARCHAR (文档 ID 241358.1)这篇文章对应这个问题。

文章解释了问题与9i和之前的版本有关，并指出同一个ORA的错误可能出现在高版本中，但根本原因与这里要描述的不同。在10.110中打开一个bug(1400539).0.1版本重写修复了这个bug。

Problem: GETTING ORA-1461 WHEN INSERTING INTO A VARCHAR FIELDProblem symptoms

• (1) Using PRO*C or OCI.
• (2) Database character set is set a multibyte character set. For example UTF8, AL32UTF8， JA16SJIS or JA16EUC.
• (3) Trying to insert a VARCHAR2 into a column that is defined with a length of more than 1333 bytes.
• (4) The same table either has another LONG column or at least 1 other VARCHAR2 with a length over 1333 bytes.
• (5) NLS_LANG is set to a single-byte character set. For example american_america.WE8ISO859

Resulting error

ORA-1461: "can bind a LONG value only for insert into a LONG column"

从这里可以看出，造成这个问题的原因之一是使用(1)PRO*C，其他可能的原因：

(5). proc应用环境字符集：

>echo $NLS_LANG

AMERICAN_AMERICA.ZHS16CGB3128

(2). 查看数据库字符集：

>SELECT * FROM nls_database_parameters;

NLS_CHARACTERSET ZHS16GBK

“When connecting to a UTF8 server, then all character lengths are multiplied by 3 since this is the maximum length that the data could take up on the server.The maximum size of a VARCHAR2 is 4000 bytes. Anything bigger will be treated as a LONG. During run-time no check is made for the actual content of the columns. Even if a VARCHAR2(2000) column only contains 1 character, this is treated as if you're using a LONG (just like a LONG that contains only 1 character). If you have 1 of these columns plus a LONG, or simply 2 or more of these columns, effectively the database believes that you are binding 2 long columns. Since that is not allowed you receive this error.”

文章提到了一个场景，即当连接UTF8字符集的数据库时，所有字符长度都需要乘以3，因为这是字符集数据需要占用的空间。VARCHAR2类型的最大长度为4000字节，任何更大的存储值都将被视为LONG。

运行时不检查列的实际内容。即使VARCHAR2(2000)列只包含一个字符，它也会按LONG处理，就像使用一个包含一个字符的LONG字段一样。如果有这样的列，加上LONG列，或者有两个或两个以上的列，数据库会认为你绑定了两个LONG列。所以这个错误会被报告。

MOS给出了四种错误的workaround方法：

1. Limit the size of the buffer from within the OCI code

2. Use the database character set also as the client character set

3. Decrease the size of the columns

4. Do not use the multibyte character set as the database character set

针对我的问题，

1. 我在这里使用char数组，估计改为varcharproc类型，限制字符长度，类似于这个oci限制字符长度，但是源于能量，没有使用。

2. 事实上，这种方法类似于imp/exp导出时遇到的字符集问题的解决方案，以避免字符集不一致带来的问题。

3. “If you make sure that there is only 1 LONG and no VARCHAR > 1333 bytes, OR just 1 VARCHAR > 1333 bytes in the table, you cannot hit this problem.“如果确认这个表只有一个LONG类型，没有超过1333字节的VARCHAR类型，或者只有超过1333字节的VARCHAR类型，就可以绕过这个问题。这取决于应用程序的业务逻辑和数据库设计是否可以匹配。

4. 这也是对字符集引起的“乘3”问题的回避。

最后一种方法是使用10.1.0.1及以上版本，不会有这样的问题。

3. PLSQL 不同于Developer或SQLPLUS和proc的错误报告：

使用PLSQL的原因是使用PLSQL Developer或SQLPLUS没有遇到这样的问题，因为它们使用不同于proc的驱动程序，proc也使用OCI连接数据库，所以这意味着Using PRO*C or 两种OCI。

实验：

针对上述说明，进行以下实验验证：

(1) a在proc中首先声明，b，c，l四个变量，赋予初值：

char a[4001], b[4001], c[4001]; long l;

memset(a, 0, sizeof(a)); memset(b, 0, sizeof(b)); memset(c, 0, sizeof(c));

strcpy(a, "a");

l = 1;

strcpy(b, "b");

strcpy(c, "c");

(2) 用proc插入记录建立测试表：

create table TBL_LV1 ( L LONG, B VARCHAR2(10)， C VARCHAR2(10) );

INSERT ... L, B VALUES(:l, :b);

可插入。

INSERT ... L, B, C VALUES(:l, :b, :c);

报错。

create table TBL_LV1 ( L LONG, B VARCHAR2(10) );

VARCHAR2(1334)、VARCHAR2(4000)

INSERT ... L, B VALUES(:l, :b);

可插入。

create table TBL_LV1 ( A VARCHAR2(10)， B VARCHAR2(10) );

INSERT ... A, B VALUES(:a, :b);

报错。

但使用

INSERT ... A, B VALUES('a', 'b');不报错。

即使改为：

create table TBL_LV1 ( A VARCHAR2(4000)， B VARCHAR2(4000) );

INSERT ... A, B('a', 'b');也不报错。

总结：

1. 如果使用proc连接9i库，由于客户端和服务端的多字节字符问题，在插入VARCHAR2类型时会出现ORA-01461: can bind a LONG value only for insert into a LONG column报错。但使用PLSQL 非OCI驱动，如Developer或SQLPLUS，不会报错。

2. 根据上述实验，使用proc绑定变量会使ora-01461的错误更加混淆。

3. 以上问题仅出现在9i及以下版本中，10.1.0.1版本修复了bug。如果仍使用9i及以下版本，oracle提供以下四种workaround：

1. Limit the size of the buffer from within the OCI code(使用OCI驱动时限制buffer的大小(4000)

2. Use the database character set also as the client character set(数据库端与客户端字符集一致)

3. Decrease the size of the columns(根据字符集的长度限制，减少列长)

4. Do not use the multibyte character set as the database character set(不要用多字节字符集作为数据库字符集)