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

Laury

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.

L. Fernigrini

Try running:

SELECT * FROM [email protected];

In order to know if the connection is established OK

mxallen-Oracle

Add the following to the DG4ODBC parameter file-

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

Regards,

Matt

Laury

mxallen-Oracle schreef:Add the following to the DG4ODBC parameter file-HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1Regards,Matt

Why to add this parameter?

Laury

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

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

For this new error ...

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

Matt

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?

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

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

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?