一张表有多个VARCHAR2类型

bisal

Yong Huang 发表于 2014-8-12 21:37
chwp,

Are you saying PLSQL Developer (I think you mean SQL Developer?) is not using OCI? When I i ...

我又试了下：
分别使用9i和10g创建以下表：
create table tbl_varchar(a varchar2(4000), b varchar2(4000), c varchar2(4000));

使用proc相同的程序，执行INSERT操作，结论是9i就是会报ORA-01461: can bind a LONG value only for insert into a LONG column的错误，10g正常插入。

请问这是因为版本特性？proc是使用的SQLLIB方式。

Yong Huang

Parameter nls_length_semantics is byte for both databases?

Sorry I don't have enough experience with Pro*C. You may want to open an SR with Oracle. I searched on the Internet and found these

http://bytes.com/topic/oracle/an ... ough-pro-c-utf-8-db
http://www.databaseskill.com/682006/
http://www.itpub.net/thread-1828787-1-1.html

It's hard to come up with good keywords to search. MyOracleSupport is malfunctioning when it comes to a keyword with * in the middle.

bisal

Yong Huang 发表于 2014-8-21 00:16
Parameter nls_length_semantics is byte for both databases?

Sorry I don't have enough experience w ...

谢谢您，我也在查找这个问题，看看有没有解

bisal

Yong Huang 发表于 2014-8-21 00:16
Parameter nls_length_semantics is byte for both databases?

Sorry I don't have enough experience w ...

我提了一个问题到社区：
https://community.oracle.com/message/12599901
得到回复，可能由于9i的bug：241358.1

Yong Huang

Excellent! Thanks for this feedback.

bisal

Yong Huang 发表于 2014-8-26 05:42
Excellent! Thanks for this feedback.

但是黄老师，其中有句话：
“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.”
包含仅仅1个字符的VARCHAR2(>2000)类型，也会按照LONG类型操作，因为一张表只允许一个LONG类型字段，所以如果仅有这样一个的VARCHAR2(>2000)做INSERT不会报错，但若大于1个，则会报这种错误。不理解这为什么“包含仅仅1个字符的VARCHAR2(>2000)类型，也会按照LONG类型操作”？感觉这是我上面proc程序中报错的根本原因。

Yong Huang

You must be reading

Workarounds for bug 1400539: GETTING ORA-1461 INSERTING INTO A VARCHAR (Doc ID 241358.1)

The bug is fixed in 10.1.0.1. So there's really not much need to make it a big deal. But this document about the bug is indeed confusing. At the beginning, it says "A character from the client character set (for example WE8ISO8859P1), can take up to 3 bytes of storage in the database character set UTF8". Note the word "up to". That's correct. Then it says "when you try to use this table from the client, the conversion ratio is set to 1:3... When connecting to a UTF8 server, then all character lengths are multiplied by 3". Note that there's no mention of "up to". Then "Even if a VARCHAR2(2000) column only contains 1 character, this is treated as if you're using a LONG". That's hard to believe. Can you test this in Oracle 9i? One table with two columns, one LONG and one VARCHAR2(2000). Use Pro*C to insert one single character to the varchar2 column. Will you get ORA-1461? I think you'll get the error only if you insert a string longer than a certain length (1333 maybe?).

bisal

Yong Huang 发表于 2014-9-8 23:04
You must be reading

Workarounds for bug 1400539: GETTING ORA-1461 INSERTING INTO A VARCHAR (Doc I ...

黄老师，我尝试了：
CREATE TABLE tbl_lv (a LONG, b VARCHAR2(2000));
当用PROC插入B字段时，无论多长，即使大于1333，也是可以正常操作的。

但如果向A和B同时插入，则会报这个错误。

Yong Huang

> 但如果向A和B同时插入，则会报这个错误。

So, even if column b takes a single character, it won't work if column a is also being inserted int? I mean,

insert into tbl_lv values ('x','x');

will report ORA-1461?

bisal

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

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

没错，即使是proc中插入a和b字段都只有一个字符，就会报-1461, ORA-01461: can bind a LONG value only for insert into a LONG column。
用SQLPLUS命令行插入两个字段则可以。