Forum Stats

  • 3,769,358 Users
  • 2,252,956 Discussions
  • 7,874,999 Comments

Discussions

Unable to connect from Oracle 19c to PostgreSQL 10.3

Laury
Laury Member Posts: 1,656 Silver Badge
edited May 20, 2019 8:10AM in Heterogeneous Connectivity

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

Best Answer

  • Laury
    Laury Member Posts: 1,656 Silver Badge
    edited May 19, 2019 5:18AM Accepted Answer

    It might be interesting to know for other persons wishing to build up such connection between Oracle RDBMS 19.x and PostgreSQL 10.x (Open Source):

    I have solved the isue by replacing the pointer to the library for the default driver location in the ODBC configuration manager file by this one:

    Driver = /usr/lib64/psqlodbc.so.

    Then, the connection using the database link is working, providing that columns and tables in the query are enclosed with double quotes as PostgreSQL is case-sensitive.

    Laury
«1

Answers

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,666 Silver Crown
    edited May 14, 2019 7:40AM

    Try running:

    SELECT * FROM [email protected];

    In order to know if the connection is established OK

  • mxallen-Oracle
    mxallen-Oracle Member Posts: 342 Employee
    edited May 14, 2019 8:45AM

    Add the following to the DG4ODBC parameter file-

    HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

    Regards,

    Matt

  • Laury
    Laury Member Posts: 1,656 Silver Badge
    edited May 14, 2019 9:45AM
    mxallen-Oracle schreef:Add the following to the DG4ODBC parameter file-HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1Regards,Matt

    Why to add this parameter?

  • Laury
    Laury Member Posts: 1,656 Silver Badge
    edited May 14, 2019 9:45AM
    L. Fernigrini schreef:Try running:SELECT * FROM [email protected];In order to know if the connection is established OK

    Here is the result:

    SQL> select * from [email protected];

    select * from [email protected]

                       *

    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?

  • mxallen-Oracle
    mxallen-Oracle Member Posts: 342 Employee
    edited May 14, 2019 9:48AM

    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

  • mxallen-Oracle
    mxallen-Oracle Member Posts: 342 Employee
    edited May 14, 2019 9:51AM

    For this new error ...

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

    Matt

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,666 Silver Crown
    edited May 14, 2019 10:43AM

    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?

  • Laury
    Laury Member Posts: 1,656 Silver Badge
    edited May 15, 2019 2:30AM
    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 [email protected];

    ---

    SQL> select * from [email protected];

    select * from [email protected]

                       *

    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

    ---

  • Laury
    Laury Member Posts: 1,656 Silver Badge
    edited May 15, 2019 2:34AM
    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

    ---

  • Laury
    Laury Member Posts: 1,656 Silver Badge
    edited May 15, 2019 2:42AM
    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?