oracle-databaseoracle12cnls-lang

Oracle database encoding problems


We have two Oracle databases. We read data from database1 in database2 through dblink. In database1 we have NLS_CHARACTERSET = AL32UTF8 and in database2 NLS_CHARACTERSET = EE8ISO8859P2.

The problem is that reading data in database2 it's wrong. Some special characters are wrong. For example:

Data in database1: ÎNTREPRINDERE INDIVIDUALĂ

Data in database2: ?NTREPRINDERE INDIVIDUAL?

Can someone explain why (bytes representation ) and what are the solutions (are more than the one to modify nls_characterset)?


Solution

  • The typecasting I suggested in my comment seems to work (for me).

    Demo

    I have an AL32UTF8 remote DB and an EE8MSWIN1250 local DB to my avail, so I had to use something different than Romanian as those chars are present in the cp1250 codepage. Went for some Russian, then.

    At remote DB...

    create table test_the_charset (xxx varchar2(256 char));
    
    insert into test_the_charset (xxx)
    values (n'ÎNTREPRINDERE INDIVIDUALĂ');
    
    insert into test_the_charset (xxx)
    values (n'Сегодня замечательный день, чтобы помочь людям на StackOverflow.com.');
    
    commit;
    

    At local DB...

    SQL> select xxx from test_the_charset@my_remote_db;
    
    XXX
    --------------------------------------------------------------------------------
    ÎNTREPRINDERE INDIVIDUALĂ
    ??????? ????????????? ????, ????? ?????? ????? ?? StackOverflow.com.
    
    SQL> select cast(xxx as nvarchar2(2000)) from test_the_charset@my_remote_db;
    
    CAST(XXXASNVARCHAR2(2000))
    --------------------------------------------------------------------------------
    ÎNTREPRINDERE INDIVIDUALĂ
    Сегодня замечательный день, чтобы помочь людям на StackOverflow.com.
    
    SQL> 
    

    As for "why this happens"...

    I don't know. ISO8859-2 should, by definition, allow for storing Romanian characters, hence they should get transferred via DB link without problems. Yet, in your case, they don't.