Oracle database encoding problems

Question

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)?

Answer 1

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.