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 in the customer segment, code corruption occurs.
 
Especially during PLSQL operations, the bug of PLSQL may cause some bad code that does not seem to be 'normal'. The following is an explanation:
 
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 segment. 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 converts the customer segment that is not utf8 during query. The conversion is incorrect at this time, 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 customer segment 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.
 

Author kkdelta