From: PropAAS DBA <dba@propaas.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: oracle_fdw issues
Date: Tue, 1 Aug 2017 19:58:20 -0600
Message-ID: <341df494-f543-d4fa-a6b7-e1288b56d66b@propaas.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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 connect**ion for foreign table "oratab1" cannot be
established
**DETAIL: ORA-12154: TNS:could not resolve the connect identifier
specified*
*
*
Thoughts?
Thanks in advance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: dba@propaas.com
Subject: Re: oracle_fdw issues
In-Reply-To: <341df494-f543-d4fa-a6b7-e1288b56d66b@propaas.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox