followed this script to try to query postqresql from an oracle server:
https://dbaspot.wordpress.com/2013/05/29/how-to-access-postgresql-from-oracle-database/ and having some trouble.
oracle server: Database Patch Set Update : 11.2.0.4.160119
postgresql server: 9.4.6
oracle> rpm -qa | grep -i odbc
postgresql95-odbc-09.05.0100-1PGDG.rhel6.x86_64
oracle > rpm -ql postgresql95-odbc-09.05.0100-1PGDG.rhel6.x86_64
/usr/pgsql-9.5/lib/psqlodbc.so
/usr/pgsql-9.5/lib/psqlodbcw.so
/usr/share/doc/postgresql95-odbc-09.05.0100
/usr/share/doc/postgresql95-odbc-09.05.0100/license.txt
/usr/share/doc/postgresql95-odbc-09.05.0100/readme.txt
--
oracle> cat .odbc.ini
[ODBC Data Sources]
PG_LINK = PostgreSQL
[PG_LINK]
Debug = 1
CommLog = 1
ReadOnly = no
Driver = /usr/pgsql-9.5/lib/psqlodbc.so
Servername = pghost
FetchBufferSize = 99
Username = pguser
Password = pgpass
Port = 5432
Database = mydb
[Default]
Driver = /usr/lib64/liboplodbcS.so
--
listener.ora
-------------
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC=
(SID_NAME=PG_LINK)
(ORACLE_HOME=/apps/database/oracle/product/11.2.0)
(PROGRAM=dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orahost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /apps/database/oracle/product
--
oracle> cat initPG_LINK.ora
HS_FDS_CONNECT_INFO = PG_LINK
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME=/usr/pgsql-9.5/lib/psqlodbc.so
set ODBCINI=
/apps/database/oracle/.odbc.ini
--
tnsnames:
----------
PG_LINK =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=pghost)(PORT=5432))
(CONNECT_DATA=(SID=PG_LINK))
(HS=OK)
)
--
listener status:
-----------------
Services Summary...
Service "PG_LINK" has 1 instance(s).
Instance "PG_LINK", status UNKNOWN, has 1 handler(s) for this service...
(asks himself why the oracle listener needs a service...it's not answering requests??? clueless me)
--
created db link in ora:
-----------------------
create database link PG_LINK connect to "pguser" identified by "pgpass" using 'PG_LINK';
SQL> select count(*) from mytable@PG_LINK;
select count(*) from circuit_tracking_event@PG_LINK
*
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from PG_LINK
any clue will do....