Hi,
I successfully configured and used Oracle's Heterogeneous Service to pull postgres data from Oracle DB. (setup the odbc.ini, initPG.ora under $ORACLE_HOME/hs/admin, configured another listener for it, completed the postgres steps)
While all works fine on SQLPLUS ( sqlplus on the DB server and sqlplus from a another remote DB server), the same select fails from TOAD on my desktop.
I can connect to the Oracle Database (TSTDB) from where the sql selecting postgres data needs to be run using TOAD, but select sql fails.
PG_MYTRACE is the DB link.
PG=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = TSTHOST)(PORT = 1524))(CONNECT_DATA =(SID = PG))(HS = OK)) --included in server and client tnsnames.ora on my desktop
CREATE PUBLIC DATABASE LINK PG_MYTRACE
CONNECT TO "dblinkmytrace"
IDENTIFIED BY <Password>
USING 'PG';
SQL> select count(*) from "mytracedb_01"."detailedrecallreportview"@PG_MYTRACE;
COUNT(*)
----------
1492924
Please help!