This discussion is archived
13 Replies Latest reply: Jul 2, 2013 9:46 PM by kgronau RSS

Connect Oracle to Postgres with UTF8

user478514 Newbie
Currently Being Moderated

Hello all.

 

I want to connect a Oracle 10gR2 in AL32UTF8 database to a Postgres 9.2 en_US.UTF-8 database.

 

Everything works if I have HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1 or HS_LANGUAGE = american_america.we8mswin1252. Not everything... accentuated characters of my main language (Portuguese) don't get visible.

 

If I set it to AMERICAN_AMERICA.AL32UTF8, i get this message:

 

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

c

 

 

Here are my configuration files:

 

odbc.ini:

[MoodlePostgres]

Description     = Moodle Postgres DB

Driver          = /usr/pgsql-9.2/lib/psqlodbcw.so

Server          = ifbBDPGMoodle

Database        = webbanca3

LogonID         =

Password        =

Port            = 5432

Socket          =

Option          =

Stmt            =

QuotedId    = YES

CHARSET     = en_US.UTF-8

 

[ODBC Data Sources]

MoodlePostgres = PostgreSQL

 

 

odbcinst.ini:

[PostgreSQL]

Description    = ODBC for PostgreSQL

Driver        = /usr/pgsql-9.2/lib/psqlodbcw.so

Setup        = /usr/lib64/libodbcpsqlS.so

Driver64    = /usr/pgsql-9.2/lib/psqlodbcw.so

Setup64        = /usr/lib64/libodbcpsqlS.so

FileUsage    = 1

 

 

tnsnames.ora:

webbanca3=

    (DESCRIPTION=

        (ADDRESS=

            (PROTOCOL=TCP)

            (HOST=172.18.1.34)

            (PORT=1521)

        )

        (CONNECT_DATA=

            (SID=webbanca3)

        )

        (HS=OK)

    )

 

listener.ora:

LISTENER =

(ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))

)

 

SID_LIST_LISTENER=

  (SID_LIST=

      (SID_DESC=

         (SID_NAME=webbanca3)

         (ORACLE_HOME=/home/moodlepostgres/product/11.2.0/tg_1)

         (PROGRAM=dg4odbc)

     (ENVS=LD_LIBRARY_PATH=/usr/pgsql-9.2/lib:/home/moodlepostgres/product/11.2.0/tg_1/lib)

      )

  )

 

#CONNECT_TIMEOUT_LISTENER = 0

 

initi<sid>.ora:

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = MoodlePostgres

#HS_FDS_TRACE_LEVEL = 0

HS_FDS_SHAREABLE_NAME = /usr/pgsql-9.2/lib/psqlodbc.so

#HS_FDS_SHAREABLE_NAME = /usr/pgsql-9.2/lib/psqlodbcw.so

HS_FDS_SUPPORT_STATISTICS = FALSE

 

#HS_LANGUAGE = american_america.we8mswin1252  # WORKS ... kinda... :-)

 

HS_LANGUAGE = american_america.al32utf8 # DOESN'T WORK

 

HS_KEEP_REMOTE_COLUMN_SIZE = ALL

 

 

#

# ODBC specific environment variables

#

set ODBCINI=/usr/local/etc/odbc.ini

set ODBCINST=/usr/local/etc/odbcinst.ini

 

 

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

 

The result of odbcinst -j is:

unixODBC 2.3.1

DRIVERS............: /usr/local/etc/odbcinst.ini

SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini

FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources

USER DATA SOURCES..: /usr/loca/etc/odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

 

I'm running on Oracle Linux 6

 

Thank you.

  • 1. Re: Connect Oracle to Postgres with UTF8
    kgronau Guru
    Currently Being Moderated

    in your gateway init file you're using HS_FDS_SHAREABLE_NAME = /usr/pgsql-9.2/lib/psqlodbc.so but commonly HS_FDS_SHAREABLE_NAME needs to refer to the ODBC Driver Manager. At the end you provided the output of odbcinst which indicates that you have a unixODBC driver installed. Please change the parameter HS_FDS_SHAREABLE_NAME and make sure that it refers to the unixODBC driver manager library (libodbc.so).

     

    In the past, when I had to initialize a Unicode connection to the Postgres database I was using the odbc.ini parameter

    ConnSettings=SET CLIENT_ENCODING to 'UNICODE'

    rather then

    CHARSET = en_US.UTF-8

    Are you really sure that this parameter initializes a Unicode connection to the Postgres database? Did you check it with isql, the ODBC test tool shipped with unixODBC?

     

    - Klaus

  • 2. Re: Connect Oracle to Postgres with UTF8
    user478514 Newbie
    Currently Being Moderated

    Hello.

    I changed the HS_FDS_SHAREABLE_NAME to the suggested libodbc.so. But the only thing that changed was the last character in the error message from Oracle.

     

    I used the parameter as you sugested in the odbc.ini. And removed the CHARSET one. Is it really like you described "ConnSettings=SET CLIENT_ENCODING to 'UNICODE' " ?

    Anyway, nothing changed.

     

    And yes, in a isql connection using the configured odbc I get a good result. I can connect and the select returns the proper portuguese characters. In Sqlplus I still don't have the portuguese characters.

     

    Any other ideas?

     

    Thank you very much.

  • 3. Re: Connect Oracle to Postgres with UTF8
    kgronau Guru
    Currently Being Moderated

    First provide me the output from your PostgreSQL database using Postgres psql tool once you connected to your database:

    show client_encoding

    show server_encoding

     

    and now let's create a demo table in PostgreSQL with a numeric column (col1) and a character column(col2). Then insert into the numeric column a number and into the second column just one Portuguese character. Redo the same for some more characters.

    Now please provide the output of the select statement:

    select col1, col2, ascii(col2) from <your table>;

    as well as the psql command "\encoding".

     

    Enable Gateway tracing level 255 in the gateway init file (HS_FDS_TRACE_LEVEL=255) and open SQL*Plus and execute this select using the gateway:

    select "col1","col2", dump("col2,16") from "<your PostgreSQL table>@<your database link>;

    and post its output.

     

    Make sure to disable gateway tracing by commenting out the trace parameter or setting the trace level to 0 and upload the gateway trace to a public file server (like Dropbox) ad provide me its link.

     

    - Klaus

  • 4. Re: Connect Oracle to Postgres with UTF8
    user478514 Newbie
    Currently Being Moderated

    Ok, Here comes what you asked:

     

    Connection using ODBC: isql MoodlePostgres ifbmoodle iofrbc1980 -v

     

    show client_encoding : UNICODE

    show server_encoding : UTF8

     

    Created table teste.

     

    Select gives me:

    +-------------------------------+-----+------------+

    | col1                          | col2| ascii      |

    +-------------------------------+-----+------------+

    | 1                             | ç  | 231        |

    | 2                             | ã  | 227        |

    | 3                             | â  | 226        |

    +-------------------------------+-----+------------+

     

    psql \encoding gives:

    UTF8

     

    sqlplus gives me the same error:

     

    ERROR at line 1:

    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

    [

     

    and trace file is here:

    http://ubuntuone.com/0fWxHuhaGY4YmgQrUzfWGd

     

    Again, thank you very much.

  • 5. Re: Connect Oracle to Postgres with UTF8
    kgronau Guru
    Currently Being Moderated

    You're using this ODBC Driver Manager library and the error you get is:

    hgopoer, line 233: got native error 0 and sqlstate I; message follows...

    [

    Exiting hgopoer, rc=0 at 2013/06/14-10:51:46

    hgocont, line 2752: calling SqlDriverConnect got sqlstate I

     

    This error is commonly happening when your ODBC Driver Manger is not able to handle Unicode connect strings during initialization. You could for example purchase a commercial ODBC driver from DataDirect as their driver is able to handle it correctly, but for your case you have to set HS_LANGUAGE=AMERICAN_AMERICA.WE8MSIN1252 in your gateway init file and we need to check how to probably fix the incorrectly displayed characters.

     

    So please change the parameter setting in th gateway init file, then select from the test table and provide me the dump values. If all of the characters are displayed correctly, the please insert one that can't be displayed into the source and provide me its ascii value on the source side and its dump value as seen from Oracle.

    In addition upload again a trace file.

     

    - Klaus

  • 6. Re: Connect Oracle to Postgres with UTF8
    user478514 Newbie
    Currently Being Moderated

    Hi. Just to see if I am understanding you.

     

    unixODBC driver, wich is what I'm using, doesn't work with UNICODE, is that it?

     

    Or just my version of the driver? If so, is there a unixODBC or any other driver, free, which works with UNICODE?

  • 7. Re: Connect Oracle to Postgres with UTF8
    user478514 Newbie
    Currently Being Moderated

    Ok. With that character set I get this select from the Oracle side (no errors):

     

      col1 col2

    ---------- ------

    DUMP(COL2,16)

    --------------------------------------------------------------------------------

         1 ç

    Typ=96 Len=6: c3,83,c2,a7,20,20

     

         2 ã

    Typ=96 Len=6: c3,83,c2,a3,20,20

     

         3 â

    Typ=96 Len=6: c3,83,c2,a2,20,20

     

    Also, the trace here:

     

    http://ubuntuone.com/20Js1JYWjCVOozr31xEvFF

  • 8. Re: Connect Oracle to Postgres with UTF8
    kgronau Guru
    Currently Being Moderated

    The root cause of your problem is the unixODBC Driver manager.

     

    In my environment I can select fine from the Postgres Unicode database storing your characters when I use in my odbc.ini

    ConnSettings=SET CLIENT_ENCODING to 'UNICODE'

    and in my gateway init these  settings:

    HS_NLS_NCHAR = UCS2

    HS_LANGUAGE=american_america.al32utf8

     

    SQL> select * from "public"."unicode"@POSTGRESQL_DG4ODBC_EMGTW_1123_DB;

     

          col1 col2                 col3

    ---------- -------------------- --------------------

             1 a                    a

             2 ä                    ä

             3 æ                    æ

             4 ç                    ç

             5 ã                    ã

             6 â                    â

     

    as soon as I change in my gateway init file the HS_LANGUAGE to an 8bit character set, for example to:

    HS_LANGUAGE=american_america.we8iso8859p1

    and

    ConnSettings=SET CLIENT_ENCODING to 'ASCII'

    I get the same corrupted content like you get:

    SQL> /

     

          col1 col2                                                                                       col3

    ---------- ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------

             1 a                                                                                          a

             2 ä                                                                                         ä

             3 æ                                                                                         æ

             4 ç                                                                                         ç

             5 ã                                                                                         ã

             6 â                                                                                         â

     

    6 rows selected.

     

     

    Your unixODBC does not accept multibyte connect strings and fails with the error 28500 and an opening bracket '[' when you set a Unicode character set.

    Did you compile unixODBC from source using --with-iconv-char-enc=UTF-8 option?

     

    Looking at the trace file shows that you're still using 11.2.0.1 gateway release and it also seems you have set in your environment a language parameter like NLS_LANG:

     

     

    First of all I would like to ask you to use 11.2.0.3 as this contains some major NLS fixes. In addition, could you then please unset any NLS_LANG variable before starting the gateway listener?

    - Klaus

  • 9. Re: Connect Oracle to Postgres with UTF8
    user478514 Newbie
    Currently Being Moderated

    Hello. Sorry for the long delay in answer.

     

    No, I did not compile unixODBC from source. I will do that with that option on the compile command.

     

    I will try your 2 requests, upgrade gateway and remove NLS_LANG. All this with the new unixODBC driver compiled from source.

     

    As soon as I have results I will post back.

     

    Again, thank you very much for your help.

  • 10. Re: Connect Oracle to Postgres with UTF8
    user478514 Newbie
    Currently Being Moderated

    Ok. Installed the Gateway 11.2.0.3 in a new home.

     

    copied the listener and tnsnames to it.

     

    altered the Oracle Home env variable to the new home.

     

    compiled unixODBC with the new option you mentioned.

     

    rebooted the machine

     

    confirmed the new Oracle Home env is set to the new home of the 11.2.0.3.

     

    Tested the isql, is OK.

     

    changed the parameter of the init...ora to the 2 options you mentioned and also confirmed the conn option on odbc.ini

     

    isql is perfect, from sqlplus... not. But there are differences. I don't get an error anymore. Simply the characters show up wrong.

     

    Strangely, I set up trace level to 255 and made another try in sqlplus, but no log generated. Only with errors do a log get's generated?

     

    In odbc.ini, am I using this correctly:

    ...

    Driver     

    = /usr/pgsql-9.2/lib/psqlodbcw.so

    ...

    Supposedly it's the correct one wright?

     

    I'm also using this in odbcinit.ini:

    [PostgreSQL]

    Description     = ODBC for PostgreSQL

    Driver          = /usr/pgsql-9.2/lib/psqlodbcw.so

    Setup           = /usr/lib64/libodbcpsqlS.so

    Driver64        = /usr/pgsql-9.2/lib/psqlodbcw.so

    Setup64         = /usr/lib64/libodbcpsqlS.so

    FileUsage       = 1

     

     

    Again, thank you very much.

  • 11. Re: Connect Oracle to Postgres with UTF8
    user478514 Newbie
    Currently Being Moderated

    Wait!!!

     

    Don't really understand why, but I went to the configuration on the first installation of the gateway (11.2.0.1) and added the options you mentioned (HS...) and... IT WORKS!

     

    I'm not really understanding is why the listener is being assumed the configuration on the first gateway installation instead of the newer one...

     

    You where a life saver. I really thank you a lot. How can I give you credit?

     

    T-H-A-N-K Y-O-U!

  • 12. Re: Connect Oracle to Postgres with UTF8
    user478514 Newbie
    Currently Being Moderated

    Obvious!!! I had to change Oracle Home in listener.ora to the new home.

     

    Silly me...

     

    Again thank you very much.

  • 13. Re: Connect Oracle to Postgres with UTF8
    kgronau Guru
    Currently Being Moderated

    Glad to hear it works now. Could you then please close this thread?

    Thanks,

    Klaus