Oracle Database Discussions

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