1 2 Previous Next 16 Replies Latest reply on May 20, 2019 12:10 PM by mxallen-Oracle

    Unable to connect from Oracle 19c to PostgreSQL 10.3

    Laury

      Hi,

       

      I am trying to configure a connection from Oracle RDBMS 12.1/12.2/19.1 to PostgreSQL 10.3/9.5 through a database link.

      But it does not work.

       

      Here are my configuration files:

       

      1) odbc.ini:

       

      Location: /home/oracle

       

      Content:

      ---

      [ODBC Data Sources]

        PG = PostgreSQL

      [PG]

        Debug = 1

        CommLog = 1

        ReadOnly = no

        Driver = /usr/lib64/psqlodbc.so

        Servername = 192.168.1.105

        FetchBufferSize = 99

        Username = postgres

        Password = postgres

        Port = 5432

        Database = pagila

      [Default]

        Driver = /usr/lib64/liboplodbcS.so.2

      ---

       

      2) initPG.ora:

       

      Location: /u01/oracle/base/product/rdbms/193/hs/admin

       

      Content:

      ---

      # 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 = PG

      HS_FDS_TRACE_LEVEL = 4

      HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

      HS_FDS_TRACE_LEVEL=ON

       

      #

      # ODBC specific environment variables

      #

      set ODBCINI=/home/oracle/odbc.ini

       

       

      #

      # Environment variables required for the non-Oracle system

      #

      #set ODBCINI=/home/oracle/odbc.ini

      ---

       

      3) tnsnames.ora:

       

      Location: /u01/oracle/base/product/rdbms/193/network/admin

       

      Content:

      ---

      ANUBIS =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = oel764-odb19c-nopdb-copy-s1)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = ANUBIS)

          )

        )

       

      PG =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = oel764-odb19c-nopdb-copy-s1)(PORT = 1521))

          (CONNECT_DATA =

            (SID = PG)

          )

          (HS = OK)

        )

       

       

      LISTENER_ANUBIS =

        (ADDRESS = (PROTOCOL = TCP)(HOST = oel764-odb19c-nopdb-copy-s1)(PORT = 1521))

      ---

       

      4) listener.ora:

       

      Location: /u01/oracle/base/product/rdbms/193/network/admin

       

      Content:

      ---

      # listener.ora Network Configuration File: /u01/oracle/base/product/rdbms/193/network/admin/listener.ora

      # Generated by Oracle configuration tools.

       

      SID_LIST_LISTENER =

              (SID_LIST =

                      (SID_DESC =

                              (GLOBAL_DBNAME = ANUBIS)

                              (ORACLE_HOME = /u01/oracle/base/product/rdbms/193)

                              (SID_NAME = ANUBIS)

                      )

                      (SID_DESC =

                              (SID_NAME=PG)

                              (ORACLE_HOME=/u01/oracle/base/product/rdbms/193)

                              # PROGRAM = dg4odbc tells the listener the use the database gateway for odbc

                              (PROGRAM=dg4odbc)

                      )

              )

       

      LISTENER =

              (DESCRIPTION_LIST =

                      (DESCRIPTION =

                              (ADDRESS = (PROTOCOL = TCP)(HOST = oel764-odb19c-nopdb-copy-s1)(PORT = 1521))

                      )

                      (DESCRIPTION =

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

                      )

              )

      ---

       

      I have created a database link PG that points to tnsname PG (PostgreSQL pgila database as data source) and that connects trough the postgres user:

       

      ---

      OWNER        DB_LINK              USERNAME        HOST                           CREATED

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

      SYS          SYS_HUB                              SEEDDATA                       17-APR-19

      PUBLIC       PG                   postgres        PG                             13-MAY-19

      ---

       

      When I run this query:

       

      select * from "public"."actor"@PG;

       

      I get this error:

       

      ---

      SQL> select * from "public"."actor"@PG;

      select * from "public"."actor"@PG

                                     *

      ERROR at line 1:

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

      [

      ---

       

      When I look in the generated trace file I have:

       

      ---

      HOSGIP for "HS_FDS_TRACE_LEVEL" returned "ON"

      HOCXU_VC2_MAX=4000

      HOCXU_RAW_MAX=2000

      HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/libodbc.so"

      HOSGIP for "HS_OPEN_CURSORS" returned "50"

      HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"

      HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"

      HOSGIP for "HS_NLS_NUMERIC_CHARACTERS" returned ".,"

      HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"

      HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"

      HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"

      HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"

      HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"

      HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"

      HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"

      HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"

      HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"

      HOSGIP for "HS_FDS_TRUNC_ANSI_DATE" returned "OFF"

      HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"

      HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"

      HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"

      HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"

      HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"

      using postgres as default schema

      HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"

      hgocont, line 2823: calling SqlDriverConnect got sqlstate I

      HOA 05/14 08:49:37.834760000: (horcrces_CleanupExtprocSession) Entered!

      HOA 05/14 08:49:37.834796000: (horcrpooe_PopOciEnv) Entered!

      HOA 05/14 08:49:37.834802000: (horcrfoe_FreeOciEnv) Entered!

      HOA 05/14 08:49:37.834809000: (horcrfoe_FreeOciEnv) Exiting...

      HOA 05/14 08:49:37.834815000: (horcrfse_FreeStackElt) Entered!

      HOA 05/14 08:49:37.834820000: (horcrfse_FreeStackElt) Exiting...

      HOA 05/14 08:49:37.834826000: (horcrpooe_PopOciEnv) Exiting...

      HOA 05/14 08:49:37.834832000: (horcrces_CleanupExtprocSession) Exiting...

      ---

       

      Does someone know what the issue can be and where to investigate further?

       

      Thanks by advance for any tip(s).

       

      Kind Regards

        • 1. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
          L. Fernigrini

          Try running:

           

          SELECT * FROM dual@PG;

           

          In order to know if the connection is established OK

          • 2. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
            mxallen-Oracle

            Add the following to the DG4ODBC parameter file-

             

            HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

             

            Regards,

            Matt

            • 3. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
              Laury

              mxallen-Oracle schreef:

               

              Add the following to the DG4ODBC parameter file-

               

              HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

               

              Regards,

              Matt

              Why to add this parameter?

              • 4. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
                Laury

                L. Fernigrini schreef:

                 

                Try running:

                 

                SELECT * FROM dual@PG;

                 

                In order to know if the connection is established OK

                Here is the result:

                 

                SQL> select * from dual@PG;

                select * from dual@PG

                                   *

                ERROR at line 1:

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

                [unixODBC][Driver Manager]Data source name not found, and no default driver

                specified {IM002}

                ORA-02063: preceding 2 lines from PG

                 

                Any other idea?

                • 5. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
                  mxallen-Oracle

                  Add the parameter because it is a known fix for the error you are getting (the square bracket and sql state I)

                  That is why it was suggested.

                   

                  It is up to you if you if you wish to implement suggestions posted to this forum.

                   

                   

                  Matt

                  • 6. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
                    mxallen-Oracle

                    For this new error ...

                    Are you able to successfully test the ODBC DSN external of Oracle, wth isql for example?

                     

                    Matt

                    • 7. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
                      L. Fernigrini

                      That means that you did not configure properly the ODBC data source.

                       

                      Are you able to use that ODBC source with any other tool that supports ODBC connections?

                      • 8. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
                        Laury

                        mxallen-Oracle schreef:

                         

                        Add the parameter because it is a known fix for the error you are getting (the square bracket and sql state I)

                        That is why it was suggested.

                         

                        It is up to you if you if you wish to implement suggestions posted to this forum.

                         

                         

                        Matt

                        Yes, it looks like this:

                         

                        ---

                        # 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 = PG

                        HS_FDS_TRACE_LEVEL = 4

                        HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

                        HS_FDS_TRACE_LEVEL=ON

                        HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

                         

                        #

                        # ODBC specific environment variables

                        #

                        set ODBCINI=/home/oracle/odbc.ini

                         

                         

                        #

                        # Environment variables required for the non-Oracle system

                        #

                        #set ODBCINI=/home/oracle/odbc.ini

                        ---

                         

                        But I still get the error:

                         

                        select * from dual@PG;

                         

                        ---

                        SQL> select * from dual@PG;

                        select * from dual@PG

                                           *

                        ERROR at line 1:

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

                        [unixODBC][Driver Manager]Data source name not found, and no default driver

                        specified {IM002}

                        ORA-02063: preceding 2 lines from PG

                        ---

                        • 9. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
                          Laury

                          mxallen-Oracle schreef:

                           

                          For this new error ...

                          Are you able to successfully test the ODBC DSN external of Oracle, wth isql for example?

                           

                          Matt

                          Here is what I have tested:

                           

                          /usr/bin/isql -v PG

                           

                          ---

                          [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified

                          [ISQL]ERROR: Could not SQLConnect

                          ---

                          • 10. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
                            Laury

                            L. Fernigrini schreef:

                             

                            That means that you did not configure properly the ODBC data source.

                             

                            Are you able to use that ODBC source with any other tool that supports ODBC connections?

                            What is wrong in my ODBC data source?

                            Or maybe, what might be wrong in my ODBC driver manager?

                             

                            No, I use ODBC only to connect to PostgreSQL.

                             

                            /usr/bin/isql -v PG

                             

                            ---

                            [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified

                            [ISQL]ERROR: Could not SQLConnect

                            ---

                             

                            The file odbcinst.ini is generated during the installation of the driver.

                             

                            ls -l /etc/*.ini

                             

                            ---

                            -rw-r--r--. 1 root root 577 Sep 12  2015 /etc/odbcinst.ini

                            ---

                             

                            cat /etc/odbcinst.ini

                             

                            --

                            # Example driver definitions

                             

                            # Driver from the postgresql-odbc package

                            # Setup from the unixODBC package

                            [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

                            ---

                             

                            Any other idea?

                            • 11. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
                              mxallen-Oracle

                              You would need to check with Postgresql regarding the configuration of their ODBC driver.

                              Until that works, the gateway will not be able to use it to make a connection to the

                              Postgresql DB.

                               

                              Matt

                              1 person found this helpful
                              • 12. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
                                L. Fernigrini

                                As you have seen, neither Oracle nor isql were able to connect to the PG data source. That means, as mxallen mentioned, that the issue is not from Oracle but directly on your ODBC driver setup or ODBC connection configuration.

                                 

                                Once that is working, then we can probably help you fix any additional issue that may arise between the Oracle DB, the ODBC Gateway and the ODBC driver , but not on how the ODBC driver connects to the PostgreSQL database.

                                1 person found this helpful
                                • 13. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
                                  Laury

                                  mxallen-Oracle schreef:

                                   

                                  You would need to check with Postgresql regarding the configuration of their ODBC driver.

                                  Until that works, the gateway will not be able to use it to make a connection to the

                                  Postgresql DB.

                                   

                                  Matt

                                  So, if I well understand the issue is on the ODBC driver manager side, so in this situation the postgresql-odbc package installed with yum.

                                  Is that correct?

                                  This means the iniPG.ora or odbc.ini are not wrongly configured s thet just "points" to libraries? Am I correct?

                                  • 14. Re: Unable to connect from Oracle 19c to PostgreSQL 10.3
                                    Laury

                                    Does someone has experience in configuring external services using ODBC for Oracle RDBMS 19.x accessing PostgreSQL 10.3?

                                    1 2 Previous Next