一张表有多个VARCHAR2类型

Yong Huang

Thanks for the update. Looks like "Even if a VARCHAR2(2000) column only contains 1 character, this is treated as if you're using a LONG" is correct. What a nasty bug!

bisal

Yong Huang 发表于 2014-9-23 21:27
Thanks for the update. Looks like "Even if a VARCHAR2(2000) column only contains 1 character, this i ...

呵呵，不知道为什么有这种限制。

bisal

Yong Huang 发表于 2014-9-23 21:27
Thanks for the update. Looks like "Even if a VARCHAR2(2000) column only contains 1 character, this i ...

Bug 1400539 : GETTING ORA-1461 WHEN INSERTING INTO A VARCHAR FIELD
最后几行说：”My ct discovered this bug can be reproduced with only a single column (varchar2(4000))in the table.“，但实际只有一个VARCHAR2(4000)时是不会出现这个bug的。

bisal

Yong Huang 发表于 2014-9-23 21:27
Thanks for the update. Looks like "Even if a VARCHAR2(2000) column only contains 1 character, this i ...

有尝试了下：
create table TBL_LV1
(
  A LONG,
  B VARCHAR2(10)
);

proc可以插入，符合说的：
The same table either has another LONG column or at least 1 other VARCHAR2 with a length over 1333 bytes.
这种情况下会出现这个bug的问题

bisal

Yong Huang 发表于 2014-9-23 21:27
Thanks for the update. Looks like "Even if a VARCHAR2(2000) column only contains 1 character, this i ...

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

MOS说的5个情况，所以我觉得(3)是有问题的，至少单独有这个条件是不会出现问题的，除非两个以上的LONG或VARCHAR2(>1333)字段，此时INSERT插入两个以上的场景下，才会有这个问题。

bisal

Yong Huang 发表于 2014-9-22 23:46
> 但如果向A和B同时插入，则会报这个错误。

So, even if column b takes a single character, it won't  ...

CREATE TABLE tbl_lv (a LONG, b VARCHAR2(2000));
很是奇怪，我刚执行了但没有报错，只有当有两个或以上VARCHAR2，且INSERT两个或两个以上VARCHAR2时会报错：
create table TBL_LV1
(
  A LONG,
  B VARCHAR2(2000),
  C VARCHAR2(2000)
);

bisal

Yong Huang 发表于 2014-9-22 23:46
> 但如果向A和B同时插入，则会报这个错误。

So, even if column b takes a single character, it won't  ...

貌似找到原因了。

proc中如果用：
EXEC SQL INSERT INTO TBL_LV1(A, B) VALUES('a', 'b');
不报错，即使AB是VARCHAR2(10)或VARCHAR2(4000)

proc中如果用：
EXEC SQL INSERT INTO TBL_LV1(A, B) VALUES(:a, :b);
报错，即使AB是VARCHAR2(10)或VARCHAR2(4000)

其中a，b的定义：
char a[4001], b[4001];
memset(a, 0, sizeof(a));
memset(b, 0, sizeof(b));
strcpy(a, "a");
strcpy(b, "b");

因此很可能是proc中使用char数组插入VARCHAR2类型会出现问题，若使用varchar的proc类型，可能就可以，但奇怪的是proc服务器字符集是AMERICAN_AMERICA.ZHS16CGB231280，数据库服务器字符集是ZHS16GBK，BGK都是双字符集吧，我插入a一个字符，也不存在>4000的可能呢？

Yong Huang

This is still the problem in 9i only. Correct? I'd just upgrade the database. It's sooooooooooo historical!

> proc中如果用：
> EXEC SQL INSERT INTO TBL_LV1(A, B) VALUES(:a, :b);
> 报错，即使AB是VARCHAR2(10)或VARCHAR2(4000)

Looks like using bind variables makes the case worse.