Due to historical reasons, Oracle had no Chinese character sets (such as oracle6, oracle7, and oracle7.1) in the early days. However, some users used databases since then and Stored Chinese characters with the us7ascii character set, or some users may choose a default character set, such as we8iso8859p1 or us7ascii, without any Chinese character encoding when creating a database, although this character set can be used normally in some cases, it is a mistake in principle to store Chinese character information using this character set. It will cause a series of troubles for the use and maintenance of databases. Under normal circumstances, to store Chinese characters to the database, the database character set must support Chinese characters, but it is not appropriate to set the database character set to single-byte character sets such as us7ascii. The us7ascii character set defines only 128 characters and does not support Chinese characters. In addition, if you can enter Chinese Characters in SQL * Plus, the operating system should support Chinese by default. However, if the character set in nls_lang is set to us7ascii, it is obviously incorrect, it does not reflect the actual situation of the client. However, Chinese characters are correctly displayed in actual applications. This is mainly because Oracle checks the character set settings of the database and the client, data will not be converted during the access process between the customer and the database, but this actually leads to the inconsistency between the character set of the database logo and the actually saved content. In the select process, Oracle also checks and finds that the character set settings of the database and the client are the same, so it also transfers the stored content to the client intact, the client operating system identifies that this is a Chinese character code, so it can be correctly displayed.
In this example, neither the database nor the client is set to a Chinese character set, but the Chinese character set can be displayed normally. From the application perspective, it seems that there is no problem. However, there are great risks, such as unexpected results when applying string functions such as length or substr.
For data that was migrated to Oracle8i/9i using the us7ascii character set database earlier (zhs16gbk is used), because the raw data has been stored in the us7ascii format, in this case, you can use the Oracle8i export tool, set the exported character set to us7ascii. After export, use ultraedit and other tools to open the DMP file, modify the second and third characters, and set 0001 to 0354, in this way, the data of the us7ascii character set can be correctly imported into the database of zhs16gbk.
To sum up, there are two common methods for reading and writing Oracle databases in. Net: oracleclient and oledb. Program There are two differences.
1. oracleclient is developed by Microsoft for Oracle databases and is only supported in. NET Framework 1.1. It is said that high speed and good performance are recommended. However, based on my experience, when the Oracle database server uses an English character set such as us7ascii, no matter how the character set is set, the Chinese characters read are garbled; if the server uses a Chinese character set such as zhs16gbk, no gibberish issues.
Reference Class Library: system. Data. oracleclient. dll.
Namespace: system. Data. oracleclient.
Common classes: oracleconnection, oraclecommand, oracledataadapter, oracletransaction, and oracledatareader.
Typical connection string: "Data Source = oratest; user id = Scott; Password = tiger" (Note: do not specify the provider driver ).
2. In oledb mode, Microsoft and Oracle companies provide their own oledb drivers, with few differences in usage methods. No matter what character set is used on the Oracle server, there is no garbled problem in reading/writing Chinese characters.
Similarities
Namespace: system. Data. oledb.
Common classes: oledbconnection, oledbcommand, oledbdataadapter, oledbtransaction, and oledbdatareader.
Differences
Reference Class Library: Microsoft only needs system. Data. dll. If you use the Oracle driver, although you only need to introduce system. Data. dll, you must first installOracle Data Access component for. net. Connection string: Compared with the oracleclient method, you need to add a provider, Microsoft is "provider = msdaora.1;", Oracle is "provider = 'oraoledb. oracle ';". With provider = 'oraoledb. Oracle ', you can use Oracle to store images and other large objects. provider = msdaora.1 is not supported. Set conn = server. Createobject ("ADODB. Connection ")
DNS = "provider = oraoledb. oracle.1; persist Security info = true; user id = user1; Password = pass1; Data Source = oradb"
