I've run into an odd problem with a legacy ASP application that uses Oracle 9i as its database. We recently had the DBA increase the size of a VARCHAR2 field from VARCHAR2(2000) to VARCHAR2(4000). The problem is that a NULL byte (ASCII: 0) is inserted into the 2001 character position in the string even if the string inserted is larger than 2000 characters. For example, if the application inserts a new record into the table with a string of 2500 characters, then 2501 characters are stored in the VARCHAR2 field and the NULL byte is inserted at the 2001 character position. The NULL byte is not part of the original posted data that was saved to the database and is causing us some grief. Have any of you ever come across something like this? The application is using the MSDAORA ODBC driver and I'm thinking that the driver is adding the terminating NULL character to its string buffer which may have a internal limit of 2000 characters.
我使用Oracle 9i作为其数据库的遗留ASP应用程序遇到了一个奇怪的问题。我们最近让DBA将VARCHAR2字段的大小从VARCHAR2(2000)增加到VARCHAR2(4000)。问题是即使插入的字符串大于2000个字符,也会在字符串的2001字符位置插入NULL字节(ASCII:0)。例如,如果应用程序使用2500个字符的字符串向表中插入新记录,则在VARCHAR2字段中存储2501个字符,在2001字符位置插入NULL字节。 NULL字节不是保存到数据库的原始发布数据的一部分,并且让我们感到悲伤。有没有人遇到过这样的事情?应用程序正在使用MSDAORA ODBC驱动程序,我认为驱动程序正在向其字符串缓冲区添加终止NULL字符,该字符串缓冲区可能具有2000个字符的内部限制。
Any ideas?
Thanks.
2 个解决方案
#1
I really don't think it is ORACLE doing that. You could always use RTRIM to remove any white space in your insert statement.
我真的不认为这是ORACLE那样做的。您始终可以使用RTRIM删除insert语句中的任何空格。
#2
Go for a 10046 trace level 4
寻找10046跟踪等级4
alter session set events '10046 trace name context forever, level 4';
That will create a trace file on the server which will contain the bind value (so you can see whether the null byte is part of string being pushed into the SQL).
这将在服务器上创建一个包含绑定值的跟踪文件(这样你就可以看到空字节是否是被推入SQL的字符串的一部分)。
Are there any character set oddities (eg are you using a multi-byte character set) ? The more oddities in the environment, the more likely you are to find a bug.
是否有任何字符集奇怪(例如,您使用的是多字节字符集)?环境越奇怪,你就越有可能找到一个bug。
Last thought is try a CLOB instead of a VARCHAR2(4000)
最后想到的是尝试CLOB而不是VARCHAR2(4000)
#1
I really don't think it is ORACLE doing that. You could always use RTRIM to remove any white space in your insert statement.
我真的不认为这是ORACLE那样做的。您始终可以使用RTRIM删除insert语句中的任何空格。
#2
Go for a 10046 trace level 4
寻找10046跟踪等级4
alter session set events '10046 trace name context forever, level 4';
That will create a trace file on the server which will contain the bind value (so you can see whether the null byte is part of string being pushed into the SQL).
这将在服务器上创建一个包含绑定值的跟踪文件(这样你就可以看到空字节是否是被推入SQL的字符串的一部分)。
Are there any character set oddities (eg are you using a multi-byte character set) ? The more oddities in the environment, the more likely you are to find a bug.
是否有任何字符集奇怪(例如,您使用的是多字节字符集)?环境越奇怪,你就越有可能找到一个bug。
Last thought is try a CLOB instead of a VARCHAR2(4000)
最后想到的是尝试CLOB而不是VARCHAR2(4000)