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 (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



  • 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 ...




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2