Oracle adds NULL Byte (ASCII: 0) to varchar2 string

Question

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.

Any ideas?

Thanks.

Answer 1

Tim,

I don't think it a database issue. Can you post the code portion that it's doing the update or insert? is it being done directly or via a stored procedure?

Answer 2

I really don't think it is ORACLE doing that. You could always use RTRIM to remove any white space in your insert statement.

Answer 3

Go for a 10046 trace level 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).

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.

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