On the "transcoding" process of Oracle Character set

This article will demonstrate the verification process of the Oracle character set "Transcoding" through experiments.

1.Lab Environment Description
The client is the Sql*plus program for the Windows XP operating system, and the client character set is 936 (corresponding to the Oracle ZHS16GBK character set);
The database version is Oracle 10g, and the database character set is Al32utf8;
The Nls_lang parameter is specified in the experiment.
1) Confirm the client character set
C:\>chcp
Activity code page: 936
Note: 936 corresponds to the ZHS16GBK character set for Oracle.
2) View database version information:
[Email protected]> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bi
PL/SQL Release 10.2.0.3.0-production
CORE 10.2.0.3.0 Production
TNS for Linux:version 10.2.0.3.0-production
Nlsrtl Version 10.2.0.3.0-production
3) Confirm the character set of the database:
[Email protected]> col PARAMETER for A20
[Email Protected]>col valuefor A20
[Email protected]>select *from v$nls_parameters where parameter = ' nls_characterset ';
PARAMETER VALUE
-------------------- --------------------
Nls_characterset Al32utf8

2.Two scenarios that will be involved in the experiment
Transcoding scenario: Setting the client's Nls_lang is consistent with the client character set, this is ZHS16GBK;
"Non-transcoding" scenario: Setting the client's Nls_lang is consistent with the database server-side character set, here is Al32utf8.
3.Create an experiment tableT
[Email protected]> CREATE TABLE T (x number (1), Client_characterset Varchar2 (Ten), Nls_lang varchar2), Database_ CharacterSet Varchar2 (Ten), Y VARCHAR2 (10));
Tablecreated.
[Email protected]> desc t;
Name Null? Type
----------------------------------- -------- ------------------------
X Number (1)
Client_characterset VARCHAR2 (10)
Nls_lang VARCHAR2 (10)
Database_characterset VARCHAR2 (10)
Y VARCHAR2 (10)
The T table contains five fields, which represent the sequence number, the client character set, the client Nls_lang settings, and the database server character set.
4.Two kindsNls_langInsert a single piece of data under the Set method
1) When the client's Nls_lang is set to ZHS16GBK, we insert the first record ("transcoding" scenario).
C:\>setnls_lang=american_america. Zhs16gbk
C:\>sqlplus Zj/[email protected]

Sql*plus:release 10.2.0.3.0-production on Fri Feb 5 19:21:312010
Copyright (c) 1982, 2006, Oracle. All rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the partitioning and Data Mining options
[email protected]> insert INTO t values (1, ' zhs16gbk ', ' zhs16gbk ', ' Al32utf8 ', ' Saint ');
1 rowcreated.
[Email protected]> commit;
Commit complete.
2) When the client's Nls_lang is set to Al32utf8, we insert the second record ("non-transcoding" scenario).
C:\>setnls_lang=american_america. Al32utf8
C:\>sqlpluszj/[email protected]

Sql*plus:release 10.2.0.3.0-production on Fri Feb 5 20:41:152010
Copyright (c) 1982, 2006, Oracle. All rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the partitioning and Data Mining options
[email protected]> insert INTO t values (2, ' zhs16gbk ', ' Al32utf8 ', ' Al32utf8 ', ' Saint ');
1 row created.
[Email protected]> commit;
Commit complete.
5.Two kindsNls_langSet method to view the two data just inserted
1) When the client's Nls_lang is set to ZHS16GBK ("transcoding" scenario).
[Email protected]> col x for 9
[Email protected]> Col Client_characterset for A8
[Email protected]> Col Nls_lang for A8
[Email protected]> Col Database_characterset for A8
[Email protected]> Col y for A4
[Email protected]> col Dump for A50
[Email protected]> Select t.*,dump (y,1016) dump from T order by 1;
X client_c Nls_lang Databasey DUMP
-- -------- -------- -------- ------------------------------------------------------
1 ZHS16GBK zhs16gbk Al32utf8 St Typ=1 len=3 characterset=al32utf8:e5,9c,a3
2 ZHS16GBK Al32utf8 Al32utf8? Typ=1 len=2 Characterset=al32utf8:ca,a5
2) When the client's Nls_lang is set to Al32utf8 ("non-transcoding" scenario).
[Email protected]> col x for 9
[Email protected]> Col Client_characterset for A8
[Email protected]> Col Nls_lang for A8
[Email protected]> Col Database_characterset for A8
[Email protected]> Col y for A4
[Email protected]> col Dump for A50
[Email protected]> Select t.*,dump (y,1016) dump from T order by 1;
X client_c Nls_lang Databasey DUMP
-- -------- -------- -------- ------------------------------------------------------
1 ZHS16GBK zhs16gbk Al32utf8 鍦? Typ=1 len=3 Characterset=al32utf8:e5,9c,a3
2 ZHS16GBK Al32utf8 Al32utf8 St Typ=1 len=2 characterset=al32utf8:ca,a5

Experimental conclusion
1) If possible, try to ensure that the client-side encoding (the Windows XP cmd tool can use the CHCP command to confirm), the Nls_lang parameter and the database character set are three consistent, so that the setting, whether from the performance, or from the prevention of encoding conversion is best;
2) If the purpose is to support Chinese, the database server side of the character set should try to choose the ZHS16GBK or Al32utf8 character set, which can reduce the improper "transcoding" caused by the character garbled fault;
3) (recommended) can have the Nls_lang parameter and the operation of the terminal character encoding consistent, so that the database can correctly obtain the use of the application terminal encoding, the "code conversion" will occur, but this can guarantee the correct transcoding, can prevent the wrong encoding into the database;

On the "transcoding" process of Oracle Character set