proc中插入VARCHAR2字段報(bào)ORA-01461: can bind a LONG value only for insert into a LONG c

最近做一個(gè)天信達(dá)貨運(yùn)接口的項(xiàng)目，碰到這么個(gè)詭異的問題。

背景：

使用proc寫的應(yīng)用，向數(shù)據(jù)庫插入記錄。表中有三個(gè)VARCHAR2(4000)類型的字段。注：Oracle 9i的庫。

問題：

執(zhí)行的時(shí)候提示：ORA-01461: can bind a LONG value only for insert into a LONG column

無法插入記錄，但使用PLSQL Developer或SQLPLUS手工執(zhí)行相同的SQL卻沒有問題。

然后換了一個(gè)10g的庫，用proc可以正確完成插入。

難道9i的庫，對于proc插入有什么特殊的限制？

解決過程：

1. 查詢OERR對該問題的說明：

帶著這個(gè)問題首先OERR看下1461的錯(cuò)誤，沒有任何說明。

2. 查詢MOS對該問題的說明：

接著查下MOS，Workarounds for bug 1400539: GETTING ORA-1461 INSERTING INTO A VARCHAR (文檔 ID 241358.1)，這篇文章和這個(gè)問題很對應(yīng)。

文章中說明了問題之和9i及之前版本有關(guān)，并且指出同一個(gè)ORA的錯(cuò)誤可能在高版本中出現(xiàn)，但根本原因和這里要描述的不同。開了一個(gè)bug(1400539)，在10.1.0.1版本中進(jìn)行了重寫修復(fù)了此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

從這里可以看到，產(chǎn)生這個(gè)問題的原因之一就是使用了(1)PRO*C，對于其他可能的原因：

(5). proc應(yīng)用的環(huán)境字符集：

>echo $NLS_LANG

AMERICAN_AMERICA.ZHS16CGB231280

(2). 查看數(shù)據(jù)庫字符集：

>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.”

文章提了一種場景，就是當(dāng)連接UTF8字符集的數(shù)據(jù)庫時(shí)，所有字符長度需要乘3，因?yàn)檫@是這種字符集的數(shù)據(jù)需要占據(jù)的空間。VARCHAR2類型的最大長度是4000字節(jié)，任何更大的存儲(chǔ)值都會(huì)作為LONG來看待。

運(yùn)行時(shí)不會(huì)檢查列的實(shí)際內(nèi)容。即使VARCHAR2(2000)列僅包含一個(gè)字符，它也會(huì)按照LONG處理，就像使用了一個(gè)包含1個(gè)字符的LONG字段。如果有一個(gè)這樣的列，再加上一個(gè)LONG列，或者有兩個(gè)或更多這樣的列，數(shù)據(jù)庫會(huì)認(rèn)為你正在綁定兩個(gè)LONG列。因此就會(huì)報(bào)這種錯(cuò)誤。

對于以上錯(cuò)誤的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數(shù)組，估計(jì)改為varchar的proc類型，限制其中的字符長度，和這種OCI限制字符長度會(huì)相近，但源于精力，沒有使用。

2. 這種做法其實(shí)和imp/exp導(dǎo)出時(shí)會(huì)碰到的字符集問題的解決方法類似，規(guī)避字符集不一致帶來的問題。

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.”，如果確認(rèn)這表只會(huì)有1個(gè)LONG類型，沒有大于1333字節(jié)的VARCHAR類型，或者僅僅有一個(gè)大于1333字節(jié)的VARCHAR類型，就可以繞開這個(gè)問題。這就取決于應(yīng)用的業(yè)務(wù)邏輯和數(shù)據(jù)庫設(shè)計(jì)之間是否可以匹配這種做法了。

4. 這塊也是針對字符集引發(fā)的“乘3”問題的一種規(guī)避。

最后還有一種方法，就是使用10.1.0.1及以上版本，就不會(huì)有這種問題了。

3. PLSQL Developer或SQLPLUS和proc的報(bào)錯(cuò)現(xiàn)象不同：

之所以使用PLSQL Developer或SQLPLUS沒碰到這種問題，是因?yàn)樗麄兪褂昧撕蚿roc不同的驅(qū)動(dòng)，proc也是使用了OCI來連接數(shù)據(jù)庫，因此這說的是Using PRO*C or OCI兩種。

實(shí)驗(yàn)：

針對上面的各種說明，做如下實(shí)驗(yàn)驗(yàn)證：

(1) proc中先聲明a，b，c，l四個(gè)變量且賦初值：

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ào)錯(cuò)。

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

報(bào)錯(cuò)。

但使用

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

即使改為：

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

總結(jié)：

1. 如果使用proc連接9i的庫時(shí)，由于客戶端和服務(wù)端的多字節(jié)字符問題，插入VARCHAR2類型時(shí)會(huì)出現(xiàn)ORA-01461: can bind a LONG value only for insert into a LONG column的報(bào)錯(cuò)。但使用PLSQL Developer或SQLPLUS這些非OCI驅(qū)動(dòng)，則不會(huì)報(bào)錯(cuò)。

2. 使用proc綁定變量，根據(jù)上面的實(shí)驗(yàn)來看，會(huì)讓ORA-01461這個(gè)錯(cuò)誤的產(chǎn)生更混淆。

3. 以上問題只在9i及以下版本會(huì)出現(xiàn)，10.1.0.1版本中已經(jīng)修復(fù)bug，若仍使用9i及以下版本，Oracle提供了如下四種workaround：

1. Limit the size of the buffer from within the OCI code(使用OCI驅(qū)動(dòng)時(shí)限制buffer大小(4000))

2. Use the database character set also as the client character set(數(shù)據(jù)庫端和客戶端的字符集保持一致)

3. Decrease the size of the columns(根據(jù)字符集的長度限制，減少列長度)

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)