Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Unable to connect from Oracle 19c to PostgreSQL 10.3

LauryMay 14 2019 — edited May 20 2019

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

This post has been answered by Laury on May 19 2019
Jump to Answer

Comments

Post Details

Added on May 14 2019
16 comments
3,755 views