13 Replies Latest reply: Jul 2, 2013 11:46 PM by Kgronau-Oracle RSS

    Connect Oracle to Postgres with UTF8

    user478514

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

          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

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

              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

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

                  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

                    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

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

                        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

                          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

                            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

                              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

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

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

                                  Thanks,

                                  Klaus