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 (this is good) select * from table;
[ID][Name]
0,Warszawa
1,Kraków
2,Gdańsk
from oracle using dblink: (not good)
select * from table@dblink;
[ID][Name]
0,Warszawa
1,KrakĂłw
2,GdaĹsk
/etc/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
dblink
diesctly access an Oracle database. Please describe your setup in more detail. Why don't you use oracle_fdw? That should work for the use case.convert('column_name','EE8ISO8859P2')
is most likely the wrong solution, it does not match to any of the parameters you provided. Perhaps it look ok, because your terminal uses ISO-8859-2 (or something similar) but it is certainly the wrong way of doing it.