Non-ASCII characters are corrupted on a target database when I am using a windows terminal

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
2 min read

Problem Description

If we want to replicate non-ASCII characters in the replication without Unicode character set. The record is correctly replicated on a target database, but when we check a table with the record using Window's terminal the value is corrupted. What's wrong?

Solution

One thing to consider in this scenario is a NLS character set of a database. The second thing is a client. You can set nls_lang variable in Windows but since you are using Window's terminal to see the record you also need to consider Windows' terminal settings. 

In our scenario, we're using AMERICAN_AMERICA.WE8MSWIN1252 for our database NLS enviroment. To make sure you are using correct nsl_lang you may set it the environment. There are number of ways to do it. If you have just one database in the Windows operating system and you're connecting locally the information about nsl_lang will be taken from Windows' registry. More about the topic, you can found out in MOS Doc ID 179133.1. 

Once you are sure that you are using the same nsl_lang as your database you need to check what's code page is set for Windows' terminal. You can use command chcp [http://ss64.com/nt/chcp.html] to find out a current code page. Change the code page for an appropriate one. So if you'd like to use WE8MSWIN1252 call chcp 1252. More info is the above link or in the internet.

The last thing is to change terminal's font to Lucida font.

 

Below you can find simple test scenario:

SET NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 sqlplus / as sysdba CREATE TABLE proddta.SNERT ( "ASSRCHWRD" CHAR(40 BYTE) ) TABLESPACE "USERS"; insert into proddta.SNERT values ('MONTRÉAL'); commit; -- select * from proddta.snert; EXIT SET NLS_LANG=AMERICAN_AMERICA.AL32UTF8 sqlplus / as sysdba select * from proddta.snert;

STEPS:

  1. drop the table

  2. set nsl_lang to WE8MSWIN1252 and terminal's code page to 1252 on a source server

  3. create the table and insert the record on source

  4. on a target server I leave default settings. See non-ASCII character is not displayed correctly. But it's problem of the code page and it's not a problem of dbvisit's replicate: 

a) I used select using dump function to have value in a hexadecimal form. You can see values are equal.

b) I changed code page of the terminal to 1252. Next select displayed correct values too.

 

 

Other resources:

The Correct NLS_LANG in a Microsoft Windows Environment (Doc ID 179133.1)
The Priority of NLS Parameters Explained (Where To Define NLS Parameters) (Doc ID 241047.1)
NLS_LANG Explained (How does Client-Server Character Conversion Work?) (Doc ID 158577.1)