proc中插入VARCHAR2欄位報ORA-01461: can bind a LONG value only for insert into a LONG column，varchar2ora-01461

proc中插入VARCHAR2欄位報ORA-01461: can bind a LONG value only for insert into a LONG column，varchar2ora-01461

最近做一個天信達貨運介面的項目，碰到這麼個詭異的問題。

背景：

使用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(不要使用多位元組字元集作為資料庫字元集)
javasqlBatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column

看錯誤應該是你在Long類型欄位插入了其他類型的值 中文是varchar2的
 
ORA-01461: can bind a LONG value only for insert into a LONG column

今天我也出這個問題了，實驗了一晚上，終於解決了，同時載入 classes12.jar 和nls_charset12.jar 這兩個就可以了