11 Replies Latest reply on Apr 26, 2019 5:41 PM by Eggo

    ODBC gateways connect to postgresql

    Eggo

      Hi All,

      I am experiencing issue when try to connect from Oracle to Postgresql throught odbc gateway using dblink.  My error as below:

       

      SQL> select count(*) from study@pgipollnew;

      select count(*) from study@pgipollnew

                                 *

      ERROR at line 1:

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

      ORA-02063: preceding line from PGIPOLLNEW

       

      Can anyone help?  Thank you very much.

       

      Garry

       

      I have follow the below steps as part of the install/configure Oracle Gateways ODBC.

      1. Install Oracle Gateways ODBC
      2. Install ODBC driver
      3. wget https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-6-i386/pgdg-redhat-repo-latest.noarch.rpm
      4. sudo rpm -ivh pgdg-redhat-repo-latest.noarch.rpm
      5. sudo yum install postgresql11-odbc.x86_64 unixODBC
      6. I have given the entries in /etc/odbc.ini file

      [pgipollnew]

      Description         = PostgreSQL connection to pgipollnew

      Driver              = /usr/pgsql-11/lib/psqlodbc.so

      Database            = pgipollnew

      Servername          = pgipollnew.domain

      UserName            = userpoll

      Password            = userpollpwd

      Port                = 5432

      Protocol            = 10.6

      ReadOnly            = No

      RowVersioning       = No

      ShowSystemTables    = No

      ConnSettings        =

      1. I have given entries in /etc/odbcinst.ini filr

      # Driver from the postgresql-odbc package

      # Setup from the unixODBC package

      [PostgreSQL]

      Description     = ODBC for PostgreSQL

      Driver          = /usr/pgsql-11/lib/psqlodbc.so

      #Setup          = /usr/lib64/libodbcpsqlS.so

      FileUsage       = 1

      1. Test the connectivity using isql

      ODBC_Gateway> isql -v pgipollnew

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

      | Connected!                            |

      |                                       |

      | sql-statement                         |

      | help [tablename]                      |

      | quit                                  |

      |                                       |

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

      SQL>

       

      1. Create initpgipollnew.ora in $ORACLE_HOME/hs/admin

      # This is a sample agent init file that contains the HS parameters that are

      # needed for the Database Gateway for ODBC

       

      #

      # HS init parameters

      #

      HS_FDS_CONNECT_INFO = pgipollnew

      HS_FDS_TRACE_LEVEL = 0

      HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

       

      #

      # ODBC specific environment variables

      #

      set ODBCINI=/etc/odbc.ini

       

       

      #

      # Environment variables required for the non-Oracle system

      #

      #set <envvar>=<value>

      1. Add entry in tnsnames.ora and listener.ora
      2. tnsnames.ora

      PGIPOLLNEW  =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = odbc_gateway.domain)(PORT = 1521))

      (CONNECT_DATA = (SID = pgipollnew))

          (HS = OK)

        )

       

      tnsping pgipollnew result:

       

      TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 25-APR-2019 08:21:33

       

      Copyright (c) 1997, 2016, Oracle.  All rights reserved.

       

      Used parameter files:

      /u01/app/oracle/product/12.2.0/db/network/admin/sqlnet.ora

       

       

      Used TNSNAMES adapter to resolve the alias

      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = odbc_gateway.domain)(PORT = 1521)) (CONNECT_DATA = (SID = pgipollnew)) (HS = OK))

      OK (0 msec)

       

      1. listener.ora

       

      # listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora

      # Generated by Oracle configuration tools.

       

      ADR_BASE_LISTENER = /u01/app/oracle/product/12.2.0/gateway

       

      INBOUND_CONNECT_TIMEOUT_= 0

       

      LISTENER =

      (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = odbc_gateway.domain)(PORT = 1521))

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

          )

        )

      SID_LIST_LISTENER =

        (SID_LIST =

            (SID_DESC =

      (SID_NAME=pgipollnew)

      (ORACLE_HOME=/u01/app/oracle/product/12.2.0/gateway)

               (ENVS = "LD_LIBRARY_PATH = /usr/pgsql-11/lib:/u01/app/oracle/product/12.2.0/gateway/lib")

      (PROGRAM=dg4odbc)

            )

        )

       

       

      lsnrctl status resut:

      LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 25-APR-2019 08:18:53

       

      Copyright (c) 1991, 2016, Oracle.  All rights reserved.

       

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= odbc_gateway.domain)(PORT=1521)))

      STATUS of the LISTENER

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

      Alias                     LISTENER

      Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production

      Start Date 25-APR-2019 08:17:21

      Uptime                    0 days 0 hr. 1 min. 31 sec

      Trace Level               off

      Security                  ON: Local OS Authentication

      SNMP                      OFF

      Listener Parameter File   /u01/app/oracle/product/12.2.0/gateway/network/admin/listener.ora

      Listener Log File /u01/app/oracle/product/12.2.0/gateway/diag/tnslsnr/bmw157/listener/alert/log.xml

      Listening Endpoints Summary...

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= odbc_gateway.domain)(PORT=1521)))

      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

      Services Summary...

      Service "pgipollnew" has 1 instance(s).

        Instance "pgipollnew", status UNKNOWN, has 1 handler(s) for this service...

      The command completed successfully

      1. Connect to Oracle and create database link and test

      SQL> Create database link pgipollnew connect to " userpoll " identified by " userpollpwd" using 'pgipollnew';

       

      Database link created.

       

      SQL> select count(*) from study@pgipollnew;

      select count(*) from study@pgipollnew

                                 *

      ERROR at line 1:

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

      ORA-02063: preceding line from PGIPOLLNEW

        • 1. Re: ODBC gateways connect to postgresql
          L. Fernigrini

          Always include full version of Oracle Database, and in this case also the ODBC Gateway.

           

          Have you tried by just running:

           

          SELECT * FROM dual@pgipollnew

           

          That would confirm if the DBLINK is correctly configured and you can access it. If that works OK, we need to dig into the problem.

           

          A piece of advice from previous experience working against different databases through a DBLINK, confirm that the table name is not case sensitive in PostgreSQL, I remeber having to use names enclosed in double quotes for some SQL Server and Informix scenarios.

           

          • 2. Re: ODBC gateways connect to postgresql
            mxallen-Oracle

            Are you positive you are using the 64-bit version of the ODBC driver?

            I ask for two reasons:

             

            1.  The file you downloaded:

            https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-6-i386/pgdg-redhat-repo-latest.noarch.rpm

            looks like it could be the 32-bit version based on the path name.

             

            2.  You state you are using Driver          = /usr/pgsql-11/lib/psqlodbc.so

            I would think the 64-bit driver would be in /usr/pgsql-11/lib64

             

            Regards,

            Matt

            • 3. Re: ODBC gateways connect to postgresql
              Eggo

              Hi Mxallen-Oracle,

              After getting the correct rpm and reinstall the odbc the directory still under /usr/pgsql-11/lib directory

               

               

              wget https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-6.10-x86_64/pgdg-redhat-repo-latest.noarch.rpm

              1. sudo rpm -ivh pgdg-redhat-repo-latest.noarch.rpm
              2. sudo yum install postgresql11-odbc.x86_64 unixODBC
              • 4. Re: ODBC gateways connect to postgresql
                Eggo

                Hi mxallen-oracle,

                I installed a 3rd party 64 bit odbc driver and isql test successes, listener status looks good and tnsnames.ora looks good but still getting error.

                Garry

                 

                SQL> select count(*) from study@pgipollnew;

                select count(*) from study@pgipollnew

                                           *

                ERROR at line 1:

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

                ORA-02063: preceding line from PGIPOLLNEW

                 

                 

                gateways> cat odbcinst.ini

                [PostgreSQL]

                Description=ODBC for PostgreSQL

                Driver=/usr/lib/psqlodbcw.so

                Setup=/usr/lib/libodbcpsqlS.so

                Driver64=/usr/lib64/psqlodbcw.so

                Setup64=/usr/lib64/libodbcpsqlS.so

                FileUsage=1

                =# Driver from the mysql-connector-odbc package

                =# Setup from the unixODBC package

                 

                [MySQL]

                Description=ODBC for MySQL

                Driver=/usr/lib/libmyodbc5.so

                Setup=/usr/lib/libodbcmyS.so

                Driver64=/usr/lib64/libmyodbc5.so

                Setup64=/usr/lib64/libodbcmyS.so

                FileUsage=1

                 

                [ODBC Drivers]

                Devart ODBC Driver for PostgreSQL=installed

                 

                [Devart ODBC Driver for PostgreSQL]

                Driver=/usr/local/lib/libdevatodbcpostgresql.x64.so

                (ORACLE@:/etc)

                 

                gateways> isql -v DEVART_POSTGRESQL

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

                | Connected!                            |

                |                                       |

                | sql-statement                         |

                | help [tablename]                      |

                | quit                                  |

                |                                       |

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

                 

                gateways> lsnrctl status

                LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 25-APR-2019 15:14:51

                 

                Copyright (c) 1991, 2016, Oracle.  All rights reserved.

                 

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gatewayes.domain)(PORT=1521)))

                STATUS of the LISTENER

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

                Alias                     LISTENER

                Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production

                Start Date                25-APR-2019 14:59:16

                Uptime                    0 days 0 hr. 15 min. 35 sec

                Trace Level               off

                Security                  ON: Local OS Authentication

                SNMP                      OFF

                Listener Parameter File   /u01/app/oracle/product/12.1.0/db/network/admin/listener.ora

                Listener Log File         /u01/app/oracle/diag/tnslsnr/bmw167/listener/alert/log.xml

                Listening Endpoints Summary...

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gatewayes.domain)(PORT=1521)))

                Services Summary...

                Service "pgipollnew" has 1 instance(s).

                  Instance "pgipollnew", status UNKNOWN, has 1 handler(s) for this service...

                The command completed successfully

                (ORACLE@:/etc)

                 

                boomitst> cat tnsnames.ora

                # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora

                # Generated by Oracle configuration tools.

                 

                PGIPOLLNEW  =

                  (DESCRIPTION =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = gatewayes.domain)(PORT = 1521))

                    (CONNECT_DATA = (SID = pgipollnew))

                    (HS = OK)

                  )

                • 5. Re: ODBC gateways connect to postgresql
                  L. Fernigrini

                  Have you teste what I suggeted?

                   

                  SELECT * FROM dual@pgipollnew

                  • 6. Re: ODBC gateways connect to postgresql
                    Eggo

                    Postgresql does not have dual and I did try it but still saem error.

                    • 7. Re: ODBC gateways connect to postgresql
                      Eggo

                      Here is the listener.ora file

                       

                      12cR102_home> cat  listener.ora

                      # listener163951.ora Network Configuration File: /var/tmp/listener163951.ora

                      # Generated by Oracle configuration tools.

                       

                      LISTENER =

                        (DESCRIPTION_LIST =

                          (DESCRIPTION =

                            (ADDRESS = (PROTOCOL = TCP)(HOST = gateways.domain)(PORT = 1521))

                          )

                        )

                       

                       

                      SID_LIST_LISTENER =

                        (SID_LIST =

                            (SID_DESC =

                               (SID_NAME=pgipollnew)

                               (ORACLE_HOME=/u01/app/oracle/product/12.2.0/gateways)

                               (ENVS ="LD_LIBRARY_PATH=/usr/local/lib:/u01/app/oracle/product/12.2.0/gateways/lib")

                               (PROGRAM=dg4odbc)

                            )

                        )

                      • 8. Re: ODBC gateways connect to postgresql
                        L. Fernigrini

                        Neither does SQL Server, but the sentence works since the Gateway converts it:

                         

                        https://docs.oracle.com/cd/B28359_01/gateways.111/b31043/conf_sql.htm#CIHGDGFC

                        • 9. Re: ODBC gateways connect to postgresql
                          mxallen-Oracle

                          Garry,

                          It looks like you have an SR opened for this with Oracle Support.

                          Currently, it appears your HS_FDS_SHARABLE_NAME parameter in the gateway init file is set incorrectly.

                          Its value should be the directory path and file name of the ODBC Driver Manager file, not the ODBC Driver.

                           

                          Have a look at the SR .. and follow its instructions.

                           

                          Matt

                          • 10. Re: ODBC gateways connect to postgresql
                            mxallen-Oracle

                            Garry,

                            Please mark this thread as answered since we resolved the issue in the SR.

                             

                            The highlights were:

                             

                            1.  Correcting the name of the Gateway init file

                            2.  Correct HS_FDS_SHAREABLE_NAME to point to the ODBC drver manager.

                            3.  Correctin the LD_LIBRARY_PATH set by ENVS parameter in the SID_DESC for the gateway.

                            4.  Setting HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

                            5.  Using the Postgresql 64-bit ODBC driver vice the Devart ODBC driver for Postgresql.

                             

                            Have a good weekend!

                            Matt

                            1 person found this helpful
                            • 11. Re: ODBC gateways connect to postgresql
                              Eggo

                              This has been answered.