PropAAS DBA wrote:
> 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
Since you are setting ORACLE_SID, I assume that you want a local ("bequeath")
connection to an Oracle server running on the same machine.
For that you need to set ORACLE_HOME and ORACLE_SID in the environment
of the PostgreSQL server process.
In addition you have to do the following (as stated in the README):
Foreign server options
----------------------
- dbserver (required)
The Oracle database connection string for the remote database.
This can be in any of the forms that Oracle supports as long as your
Oracle client is configured accordingly.
Set this to an empty string for local ("BEQUEATH") connections.
Yours,
Laurenz Albe