Oracle strange result survey memo, oracle memo

Oracle strange result survey memo, oracle memo

Recently, I need to maintain an ASP. Net program developed more than a decade ago. I encountered various strange problems and recorded the following problems that are hard to find:

 

Question 1:

The query results of the same SQL query vary with different servers. In the QA environment, the results are completely normal, while in the local environment, some field values are DBNull.

This is a very strange problem. The only rule found at that time was that the field with the DBNull value is of the Clob type. The ASP. Net Method for connecting to the database is OleDb. In the link string, "Provider = OraOLEDB. Oracle.1 ".

Switch to OracleClient first, and the query result is normal. The problem seems to be in OleDb. There are some information about the Clob type on the Internet, but why is there no problem on the QA server.

With this problem, I made a small tool that can execute SQL queries using different clients in. Net and put it on the QA server for running. The result is indeed normal.

The QA server environment is 32-bit, the Oracle client master version is 10, my own computer environment is 32-bit, And the Oracle client master 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 uninstalled and reinstalled, and the result remained.

Control Registry

HKEY_LOCAL_MACHINE \ SOFTWARE \ Oracle \ key_1_lient11g_home1 \ OLEDB

HKEY_LOCAL_MACHINE \ SOFTWARE \ Oracle \ ODP. NET \ 2.112.1.0

It is found that the key values are completely consistent. At this time, I began to suspect the difference between 32-bit and 64-bit. Because the tested servers are 64-bit, only the QA environment is 32-bit, and the QA environment is a client of version 10.

Finally, we found a 32-bit server with the client version of 11.

Therefore, it is estimated that the Clob field query problem of OleDb is caused by running a 32-bit client on a 32-bit system. The 64-bit client on a 64-bit system does not seem to have this problem. Whether the 32-bit client on the 64-bit system has this problem is not verified because of the time relationship. This problem seems to have nothing to do with the server, because it will be repeated on both 32-bit and 64-bit servers.

 

Therefore, this problem can be re-described: when you use a 32-bit Oracle Provider for ole db in a 32-bit system to query data that contains the Clob field type, a false null value may be returned.

There are two solutions: Use the OracleClient class to access the database or use Provider = msdaora; to access the database, this is actually the same method, both use 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. At present, it should be the Oracle Provider for ole db Bug in processing character encoding.

[Question 1: due to different system environments, this issue may not necessarily recur. Currently, it is known that the cause of this issue is the value of NLS_LANG in the Registry HKEY_LOCAL_MACHINE \ SOFTWARE \ Oracle \ key_1_lient11g_home1. In the system region and language options, if the format is China, the default value is SIMPLIFIED CHINESE_CHINA.XXXX. In this case, the query result is normal. If the format of the system region and language option is U.S., the default value of NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252. This problem is not found to be related to the NLS_LANG value on the server.]

Question 2:

The query results with special characters in Spanish are displayed differently on different servers. garbled characters are executed locally, and the QA server is normal.

This problem has also been entangled for a long time. After a variety of failed attempts, I thought of a control panel configuration item: the Administrator tab of the "Region and Language" configuration item contains the "Language for non-Unicode programs" option. Previously, I changed this configuration item to Chinese when running some Chinese software that does not use Unicode encoding. My system was originally in English version. So I changed this option back to English and solved the problem.

 

Therefore, this problem can be re-described: if the system's non-Unicode language options are incorrect when you use Oracle Provider for ole db to query content containing non-Unicode encoding fields, the query result encoding is incorrect.