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 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);

報錯。

  B VARCHAR2(10)

VARCHAR2(1334)、VARCHAR2(4000)

  A VARCHAR2(10),

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

但使用

INSERT ... A, B VALUES('a', 'b');不報錯。

即使改為：

  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(不要使用多位元組字元集作為資料庫字元集)