Skip to Main Content
Forums

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!

Heterogenous connection to PostgreSQL doesn't work, no error output from driver

User_MDNQQOct 15 2021

I've tried to configure heterogeneous connection to PostgreSQL, everything seems to work fine but after any remote query I get the following message error:
SQL> select * from "testtable"@pg_link;
select * from "testtable"@pg_link
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from PG_LINK

My configuration files:
/etc/odbc.ini
[PostgreSQL]
Description = PostgreSQL
Driver = /usr/lib64/psqlodbcw.so
ServerName = 192.168.1.14
Username = user
Password = password
Port = 5432
Database = db
Trace = yes
TraceFile = /tmp/odbctrace.txt

/etc/odbcinst.ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib64/psqlodbcw.so
Setup = /usr/lib64/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbcw.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1

$ORACLE_HOME/network/admin/listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PROJEKTRBD)
(ORACLE_HOME = /u01/app/oracle/product/21.0.0/dbhome_1\
)
)
(SID_DESC =
(SID_NAME = CBD1)
(ORACLE_HOME = /u01/app/oracle/product/21.0.0/dbhome_1\
)
)

                       (SID\_DESC =  
                                 (SID\_NAME = PostgreSQL)  
                                 (ORACLE\_HOME = /u01/app/oracle/product/21.0.0/dbhome\_1\\

)
(ENVS="LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/pr\
oduct/21.0.0/dbhome_1")
(PROGRAM = dg4odbc)
)
)

$ORACLE_HOME/network/admin/tnsnames.ora
PostgreSQL =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = localhost)
(PORT = 1521)
)
(CONNECT_DATA =
(SID = PostgreSQL)
)
(HS = OK)
)

$ORACLE_HOME/hs/admin/initPostgreSQL
# HS init parameters
HS_FDS_CONNECT_INFO = PostgreSQL
HS_FDS_TRACE_LEVEL = ON
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9

# ODBC specific environment variables
set ODBCINI=/etc/odbc.ini

When running "isql -v PostgreSQL", it works ok and I can query the remote database, so I guess the ODBC Data source is configured fine. Running "tnsping PostgreSQL" returns :
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521)) (CONNECT_DATA = (SID = PostgreSQL)) (HS = OK))
OK (10 msec)
So seemingly no problem here either. However whenever running any query from sqlplus it shows the mentioned error. No logs show up in /hs/log or /tmp, it seems like dg4odbc isn't working at all.

Comments

Post Details

Added on Oct 15 2021
#heterogeneous-connectivity
0 comments
211 views