proc中插入VARCHAR2字段报ORA-01461:canbindaLONGvalueonlyforin

最近做一个天信达货运接口的项目，碰到这么个诡异的问题。 背景 ： 使用proc写的应用，向数据库插入记录。表中有三个VARCHAR2(4000)类型的字段。注：Oracle 9i的库。 问题 ： 执行的时候提示：ORA-01461: can bind a LONG value only for insert into a LONG

最近做一个天信达货运接口的项目，碰到这么个诡异的问题。

背景：

使用proc写的应用，向数据库插入记录。表中有三个VARCHAR2(4000)类型的字段。注：Oracle 9i的库。

问题：

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

无法插入记录，但使用PLSQL Developer或SQLPLUS手工执行相同的SQL却没有问题。

然后换了一个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的错误可能在高版本中出现，但根本原因和这里要描述的不同。开了一个bug(1400539)，在10.1.0.1版本中进行了重写修复了此bug。

Problem:
GETTING ORA-1461 WHEN INSERTING INTO A VARCHAR FIELD

Problem 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.WE8ISO8859P1 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.ZHS16CGB231280

(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处理，就像使用了一个包含1个字符的LONG字段。如果有一个这样的列，再加上一个LONG列，或者有两个或更多这样的列，数据库会认为你正在绑定两个LONG列。因此就会报这种错误。

对于以上错误的workaround方法，MOS则给出了四种：

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数组，估计改为varchar的proc类型，限制其中的字符长度，和这种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.”，如果确认这表只会有1个LONG类型，没有大于1333字节的VARCHAR类型，或者仅仅有一个大于1333字节的VARCHAR类型，就可以绕开这个问题。这就取决于应用的业务逻辑和数据库设计之间是否可以匹配这种做法了。

4. 这块也是针对字符集引发的“乘3”问题的一种规避。

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

3. PLSQL Developer或SQLPLUS和proc的报错现象不同：

之所以使用PLSQL Developer或SQLPLUS没碰到这种问题，是因为他们使用了和proc不同的驱动，proc也是使用了OCI来连接数据库，因此这说的是Using PRO*C or OCI两种。

实验：

针对上面的各种说明，做如下实验验证：

(1) proc中先声明a，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 VALUES('a', 'b');也不报错。

总结：

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

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(不要使用多字节字符集作为数据库字符集)