甲骨文增加了空字节（ASCII：0）为VARCHAR2字符串 [英] Oracle adds NULL Byte (ASCII: 0) to varchar2 string

问题描述

我碰到与使用Oracle 9i中作为其数据库的传统的ASP应用程序一个奇怪的问题。我们最近有DBA增加VARCHAR2字段的VARCHAR2（2000）的大小VARCHAR2（4000）。的问题是，一个空字节（ASCII：0）被插入到该串中的2001年的字符位置，即使插入的串大于2000个字符。例如，如果应用程序中插入一个新的记录到表具有2500个字符的字符串，那么2501个字符被存储在VARCHAR2字段和空字节插入于2001年的字符位置。空字节是不是被保存到数据库中，并导致我们一些悲痛的原贴数据的一部分。有任何你曾经遇到过这样的事情？该应用程序正在使用MSDAORA ODBC驱动程序，我想，驾驶员将终止空字符到字符串缓冲区其中的可能的有2000个字符的内部限制。

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.

任何想法？

感谢。

推荐答案

走了10046跟踪级别4

Go for a 10046 trace level 4

alter session set events '10046 trace name context forever, level 4';

这将创建一个包含绑定值在服务器上的一个跟踪文件（这样你就可以看到是否空字节字符串的一部分被推入SQL）。

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

是否有任何字符集的怪事（如您正在使用一个多字节字符集）？环境越古怪，就越有可能你找到的bug。

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.

最后的想法是尝试CLOB而不是VARCHAR2（4000）

Last thought is try a CLOB instead of a VARCHAR2(4000)

这篇关于甲骨文增加了空字节（ASCII：0）为VARCHAR2字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！