0

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

Improve this question
5
  • You cannot have 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.
    – Laurenz Albe
    Mar 29, 2022 at 14:34
  • I found a temporary solution, I just added convert('column_name','EE8ISO8859P2') for each column containing text. At the moment, all special characters are displaying correctly.
    – WBGAD
    Mar 29, 2022 at 18:19
  • You are speaking in riddles, since you cannot access Oracle with dblink.
    – Laurenz Albe
    Mar 29, 2022 at 20:09
  • Duplicate of stackoverflow.com/questions/71660364/…
    – Wernfried Domscheit
    Apr 2, 2022 at 21:55
  • Using 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.
    – Wernfried Domscheit
    Apr 2, 2022 at 21:56

1 Answer 1

Reset to default
0

I would check environment variable NLS_LANG when starting the Oracle instance. If it is set wrong or not set at all funny things may happen when selecting Non US7ASCII content. Try

export NLS_LANG=American_America.AL32UTF8

Actually, only the Transparant Gateway client needs that setting - maybe there is a special parameter for it. HS_LANGUAGE seems not to be the one ...

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.