Oracle Bizarre Results Survey Memo-A investigation memo of weird Oracle database search results

The recent need to maintain an ASP. NET program that was developed almost more than 10 years ago, encountered a variety of bizarre problems, the more difficult to identify the problem is recorded as follows:

Question one:

The same SQL query results in different queries on different servers. In the QA environment, the results are completely normal, while local, partial field values are DBNull.

This is a very strange problem, the only rule that was discovered is that the field that appears DBNull value is CLOB type. Asp. NET connection to the database is OLE DB, which is "provider=oraoledb.oracle.1" in the link string.

First change to OracleClient, the query results are normal, the problem appears to be on OLE DB. There is also some information about the CLOB type on the Internet, but why is there no problem with the QA server?

With this problem, I made one that could pass. NET of different clients to execute SQL query tools, put on the QA server to run, the result is really normal.

The environment for the QA server is 32 bits, the Oracle client major version is 10, my own computer environment is 32 bits, and the Oracle client major version is 11.

Then I tried on several servers and the results were normal. I began to suspect that the Oracle client on my computer was not properly installed, so I unloaded the load and the result was still.

Against the registration form

Hkey_local_machine\software\oracle\key_oraclient11g_home1\oledb

hkey_local_machine\software\oracle\odp.net\2.112.1.0

The found key values are exactly the same. At this point I began to suspect 32-bit and 64-bit differences. Because the previously tested servers are 64-bit, only the QA environment is 32-bit, and the QA environment is the 10 version of the client.

Finally found a 32-bit environment server, the client version is 11, the problem is reproduced.

Therefore, it is speculated that the problem with OLE DB querying the Clob field is caused by running 32-bit clients on a 32-bit system, and the 64-bit clients on 64-bit systems do not seem to have this problem. There is a problem with 32-bit clients on 64-bit systems because the time relationship is not verified personally. This problem does not appear to be related to the server, because this problem is reproduced on both 32-bit and 64-bit servers.

Therefore, this problem can be re-described: Using a 32-bit Oracle Provider for OLE DB under a 32-bit system to query for data that contains CLOB field types can result in false null values.

There are two ways to do this, using the OracleClient class to access the database or using Provider=msdaora to access the database, which is actually the same approach, all using Microsoft OLE DB Provider for Oracle;

Another solution is to upgrade the server to a 64-bit environment and install the 64-bit client as much as possible. For the time being, it should be the Oracle Provider for OLE DB bug.

Question two:

The query results of special characters in Spanish are different on different servers, the local execution is garbled and the QA server is normal.

This problem has been a long time, after a variety of attempts fruitless thought of a control panel configuration item: There is a "Language for Non-unicode programs" option in the Administrator tab of the "Region and Language" configuration item. In order to run some Chinese-made software that does not use Unicode encoding, this configuration item has been changed to Mandarin, and my system was originally in English. Then change this option back to English and solve the problem.

Therefore, this problem can be re-described: When using Oracle Provider for OLE DB queries that contain non-Unicode encoded field content, if the system's non-Unicode language option is incorrect, the query result is incorrectly encoded.

Oracle Bizarre Results Survey Memo-A investigation memo of weird Oracle database search results