Oracle Character Set

Oracle character sets are often mentioned. Oracle Character Set problems are mainly caused by garbled characters. Garbled characters are generated mainly because the character sets used by clients and servers are different and are converted to character sets.

However, the conversion is mentioned many times, but it is not mentioned at which stage and where the conversion occurs? When the server writes data to the block? On the client or the server?

The correct answer is that normal String Conversion occurs on the client (specifically completed by the oci library). The country string is converted twice, and occurs on the client for the first time, the second occurs on the server. Perform the following test:

Connect:

 
 

  
  
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production  
  
  
With the Partitioning, Real Application Clusters, OLAP and Data Mining options  
  
  
SQL> select * from nls_database_parameters where parameter like ‘%CHARACTERSET%’;  
  
  
PARAMETER VALUE  
  
  
------------------------------ ------------------------------  
  
  
NLS_CHARACTERSET ZHS16GBK  
  
  
NLS_NCHAR_CHARACTERSET AL16UTF16  
  
  
SQL> create table t1(a varchar2(100));  
 
 

The table has been created.

 
 

  
  
SQL>
  
  
SQL> insert into t1 values ('中 ');
 
 

One row has been created.

 
 

  
  
SQL> 
 
 

In this connection, I have not set the NLS_LANG variable. The Oracle character set on the client is the default Character Set ZHS16GBK of the operating system. By capturing the network package, you can find the data sent from the client to the client (Images cannot be uploaded, depressing ):

 
 

  
  
00000090 00 00 00 00 00 00 00 00 00 00 00 28 DB 00 01 1C ………..(….  
  
  
000000A0 69 6E 73 65 72 74 20 69 6E 74 6F 20 74 31 20 76 insert.into.t1.v  
  
  
000000B0 61 6C 75 65 73 20 28 27D6 D027 29 01 00 00 00 alues.(’..’)….  
  
  
000000C0 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….  
  
  
 
 
 

Note that the red part indicates that the hexadecimal D6 D0 is the GBK encoding of the "medium" character. (For more information about how to obtain the encoding of Chinese characters, skip this section. If necessary, contact us again)

Now exit SQLPLUS and set the environment variable NLS_LANG:

 
 

  
  
SQL> rollback; 
 
 

Rollback completed.

 
 

  
  
SQL> exit  
  
  
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production  
  
  
With the Partitioning, Real Application Clusters, OLAP and Data Mining options  
 
 

Disconnected

 
 

  
  
C: \ Documents ents and Settings \ Administrator> set nls_lang = american_america.us7ascii
  
  
C: \ Documents ents and Settings \ Administrator> sqlplus test/test @ dmdb
  
  
SQL * Plus: Release 10.2.0.1.0-Production on Mon Jan 28 00:48:41 2008
  
  
Copyright (c) 1982,200 5, Oracle. All rights reserved.
  
  
Connected:
  
  
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
  
  
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
  
  
SQL> insert into t1 values ('中 ');
  
  
1 row created.
 
 

The captured Network Package is found to have been converted before SQL is submitted to the server. The OCI database considers that the submitted encoding is US7ASCII, so it is necessary to convert the code to the ZHS16GBK code on the server side. However, the "medium" encoding, that is, the hexadecimal D6 D0 is not a valid US7ASCII code, therefore, oracle oci is converted to the Trans-province value 3F3F (US7ASCII is a single-byte Oracle character set, and "medium" is considered to be two characters, so there are two 3F). This is "?" Number.

 
 

  
  
00000090 00 00 00 00 00 00 00 00 00 00 00 C8 1D FF 00 1C …………….  
  
  
000000A0 69 6E 73 65 72 74 20 69 6E 74 6F 20 74 31 20 76 insert.into.t1.v  
  
  
000000B0 61 6C 75 65 73 20 28 273F 3F27 29 01 00 00 00 alues.(’??’)….  
  
  
000000C0 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….  
  
  
 
 
 

Let's take a look at setting the client NLS_LANG as simplified chinese_china.zhs16cgb231280. What will happen above is an introduction to the Oracle Character Set conversion.