'How to convert UTF8 data from PostgreSQL to AL32UTF8 Oracle DB?

I have a task to import some data from Postgres database to Oracle via dblink

The connection between Postgres and Oracle works good, but unfortunately, when I try read data from created view (in Oracle database), I spotted a problem with data encoding - special national characters (Polish).

Source Postgres database have a UTF8 encoding, but Oracle have a AL32UTF8

Postgres:

select server_encoding
-

UTF8

Oracle:

select * from v$nls_parameters where parameter like '%CHARACTERSET';
-

PARAMETER VALUE

NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

When I use command "isql -v" (on destination machine with Oracle database) and later "select * from table;", everything works good, but when I use this same select from Oracle database using dblink my data encoding is broken

For example: from odbc: isql -v

select * from table;

[ID][Name]
0,Warszawa
1,Kraków
2,Gdańsk

from oracle using dblink:

select * from table@dblink;

[ID][Name]
0,Warszawa
1,KrakĂłw
2,Gdańsk

/etd/odbc.ini:

[ODBC Data Sources]

[Postgres_DB]
Description         = Postgres_DB
Driver              = /usr/lib64/psqlodbcw.so
DSN                 = Postgres_DB
Trace               = Yes
TraceFile           = /tmp/odbc_sql_postgresdb.log
Database            = database
Servername          = server
UserName            = user
Password            = secret
Port                = 5432
Protocol            = 8.4
ReadOnly            = Yes
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
SSLmode             = require
Charset             = UTF8

$ORACLE_HOME/hs/admin/initPostgres_DB.ora:

HS_FDS_CONNECT_INFO = Postgres_DB
HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS = FALSE
HS_LANGUAGE=AL32UTF8
set ODBCINI=/etc/odbc.ini

I have installed these packages:

postgresql-libs.x8664 - 8.4.20-8.el69
postgresql-odbc.x8664 - 08.04.0200-1.el6
unixODBC.x8664 - 2.2.14-14.el6
unixODBC-devel.x86_64 - 2.2.14-14.el6

Please help me.. I need to have the correct data in Oracle.. Thank you very much



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source