Impact of Character Set setting on applications on the Oracle Server Client

If the character set of the Server Client is improperly set in the Oracle database, the data stored in the database will not be properly saved, or the data in the database cannot be properly displayed on the client, code corruption occurs.

Especially when using sqlplus, due to sqlplus's "bug" (or improper use by users), it may cause some bad code that does not look 'normal'. The following explains:

1. view the character set of the database:

SQL> select * From database_properties where property_name = 'nls _ characterset
Property_name property_value description
--------------------------------------------------------------------------
Nls_characterset utf8 Character Set

2. The operating system environment of the client is Chinese. Insert a data entry through sqlplus in the default settings and submit the data:

SQL> insert into yorker. Test values ('Chinese', 'sqlplus default ');

At this time, during the insertion, the client characters, such as 'China', are encoded by the operating system (such as zhs16gbk) and then sent to the Oracle server. Oracle is converted

Utf8 storage.

3. Enable sqlplus query by default

SQL> select value, nlstype, dump (value) dv from yorker. test;

Value nlstype dv
----------------------------------------------------------------------------
Chinese sqlplus default typ = 1 Len = 6: 228,184,173,230,150,135

The display is normal. The Oracle Server reads the binary content of the field and converts the binary content to 'Chinese' Based on the client encoding. The encoding content corresponding to the client encoding is sent to the client.

Dump (value) is the hex encoded content of 'China' on the server side, in the form of utf8 content.

4. Enter sqlplus after setting nls_lang on the client

C: \> set nls_lang = american_america.utf8

C: \> sqlplus sys/sys as sysdba

SQL> select value, nlstype, dump (value) dv from yorker. test;
Value nlstype dv
--------------------------------------------------------------------------------------------------
Juan sqlplus default typ = 1 Len = 6: 228,184,173,230,150,135

The above error code is displayed in the query result because sqlplus told the Oracle server client to encode the code utf8 during the query, at this time, Oracle does not convert the utf8-encoded content 'China'

Sqlplus, but sqlplus uses the operating system code (such as zhs16gbk) to identify this content, and the result is an unrecognizable bad code. After set nls_lang = american_america.utf8, characters are not identified in the form of utf8 encoding. The content stored in Oracle is correctly implemented in the form of utf8, it can still be correctly displayed on other clients (such as the default sqlplus and Java applications)

5. Insert data in sqlplus with utf8 configured on the client

C: \> set nls_lang = american_america.utf8

C: \> sqlplus sys/sys as sysdba

SQL> insert into yorker. Test values ('Chinese', 'sqlplus utf8 ');

At this time, an "error" occurs. The "Chinese" is sent to the Oracle server in the zhs16gbk encoding format of the operating system, and sqlplus tells the server that the encoding sent is utf8, therefore, the Oracle server does not convert the 'Chinese' in the zhs16gbk encoding format to the server disk. However, Oracle considers this encoding to be utf8, oracle performs conversion when the query is not performed by the utf8 client. At this time, the conversion is incorrect, because the 'China' of the zhs16gbk encoding format may not find the corresponding character or different character in utf8.

5. query data with sqlplus configured as utf8 on the client

C: \> set nls_lang = american_america.utf8

C: \> sqlplus sys/sys as sysdba

SQL> select value, nlstype, dump (value) dv from yorker. test;
Value nlstype dv
---------------------------------------------------------------------------------------------------
Chinese sqlplus utf8 typ = 1 Len = 4: 214,208,206,196

The encoding with utf8 is correct. This is because "error is correct": sqlplus tells the Oracle client that it is utf8. Oracle directly sends the binary content stored in the database to the client without conversion, because it is consistent with the content saved, it can still be correctly displayed.

At this time, if the nls_lang set by the client is not the same as the utf8 set by the server, the server performs a 'convert' before sending it to the client, and the display is incorrect, as shown in the following example:

?? Sqlplus utf8 typ = 1 Len = 4: 214,208,206,196

In a Java client application

Private Static void testencoding () throws sqlexception {
Connection conn = nonxadbutil. getconnection ("orcl ");
Preparedstatement sta = conn. preparestatement ("select value, nlstype, dump (value) dv from yorker. test ");
Resultset rset = sta.exe cutequery ();
While (rset. Next ()){
System. Out. println (rset. getstring (1) + "" + rset. getstring (2) + "" + rset. getstring (3 ));
}
Rset. Close ();
Sta. Close ();
Conn. Close ();
}

The displayed content is as follows:

Chinese sqlplus default typ = 1 Len = 6: 228,184,173,230,150,135
�� Sqlplus utf8 typ = 1 Len = 4: 214,208,206,196

The cause of the Code corruption is that Oracle recognizes the 'Chinese' (214,208,206,196) in utf8 encoding format to identify errors, this step of converting to the characters that the Java client wants is even more 'errone.

* Principles observed during import and export,

Export: the client sets nls_lang to be consistent with the database of the data source.

Import: the encoding set when the client is set to export. If the export is completed by someone else, you must know the encoding set during the export. The data must be set to be consistent with the data exported during import.

The exported file 2nd records the encoding of the file in 3rd bytes. It can be viewed using ultraedir in hexadecimal notation. For example, if it is 03 54, convert it to 10 first to get

Select to_number ('200', 'xxxx') from dual to get 0354. You can use select nls_charset_name (852) from dual to know that it is zhs16gbk