oracle_fdw issues

[PropAAS DBA <dba@xxxxxxxxxxx>]

Hi All;

I'm trying to setup oracle_fdw and I think I'm mostly there but I have an issue.  Here's what I've done.

- both PostgreSQL 9.6 and Oracle v10 running on the same server

As the postgres user I can connect to the Oracle instance like so:

1) export ORACLE_HOME

2) export ORACLE_SID

Note: the oracle sid = VLCDB

3) run sqlplus and when prompted enter system for the user and then the passwd

So based on the fact I can connect, I did this:

1) downloaded, compiled and installed oracle_fdw

2) connected to psql and ran this:

- CREATE EXTENSION oracle_fdw;

- CREATE SERVER oradb1  FOREIGN DATA WRAPPER oracle_fdw
          OPTIONS (dbserver 'VLCDB');

-GRANT USAGE ON FOREIGN SERVER oradb1 TO postgres;

- CREATE USER MAPPING FOR postgres SERVER oradb1
          OPTIONS (user 'system', password 'orapwd');

All of the above commands completed successfully, then I created a foreign table which also succeeded:

CREATE FOREIGN TABLE oratab1 (pid int, cname varchar(30), cstatus varchar(30), c_ts timestamp with time zone)

SERVER oradb1 options (schema 'CLD', table 'cust_ord_process_status');

Now if I run:

IMPORT FOREIGN SCHEMA CLD FROM SERVER oradb1 INTO local_cld_sch;

it hangs for a long time and eventually returns this error

ERROR:  cannot connect to foreign Oracle server
DETAIL:  ORA-12154:  TNS:could not resolve the connect identifier specified

Also If I run this (based on the foreign table above)

select * from oratab1 limit 10;

it also hangs for a long time, then I get this error

ERROR:  cannot connection for foreign table "oratab1" cannot be established
DETAIL:  ORA-12154:  TNS:could not resolve the connect identifier specified

Thoughts?

Thanks in advance