To: pgsql-admin <pgsql-admin@postgresql.org>
From: PropAAS DBA <dba@propaas.com>
Subject: oracle_fdw issues
Message-ID: <341df494-f543-d4fa-a6b7-e1288b56d66b@propaas.com>
Date: Tue, 1 Aug 2017 19:58:20 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.2.1
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------0C356377579FE2C2A00D0CAA"
Content-Language: en-US
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------0C356377579FE2C2A00D0CAA
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------0C356377579FE2C2A00D0CAA
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=utf-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <p>Hi All;</p>
    <p><br>
    </p>
    <p>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.</p>
    <p><br>
    </p>
    <p>- both PostgreSQL 9.6 and Oracle v10 running on the same server</p>
    <p>As the postgres user I can connect to the Oracle instance like
      so:</p>
    <p>1) export ORACLE_HOME</p>
    <p>2) export ORACLE_SID</p>
    <p>Note: the oracle sid = VLCDB<br>
    </p>
    <p>3) run sqlplus and when prompted enter system for the user and
      then the passwd</p>
    <p><br>
    </p>
    <p>So based on the fact I can connect, I did this:</p>
    <p><br>
    </p>
    <p>1) downloaded, compiled and installed oracle_fdw</p>
    <p>2) connected to psql and ran this:</p>
    <p>- CREATE EXTENSION oracle_fdw;</p>
    <p>- CREATE SERVER oradb1  FOREIGN DATA WRAPPER oracle_fdw<br>
                OPTIONS (dbserver 'VLCDB');</p>
    <p>-GRANT USAGE ON FOREIGN SERVER oradb1 TO postgres;</p>
    <p>- CREATE USER MAPPING FOR postgres SERVER oradb1<br>
                OPTIONS (user 'system', password 'orapwd');</p>
    <p><br>
    </p>
    <p><br>
    </p>
    <p>All of the above commands completed successfully, then I created
      a foreign table which also succeeded:</p>
    <p>CREATE FOREIGN TABLE oratab1 (pid int, cname varchar(30), cstatus
      varchar(30), c_ts timestamp with time zone)</p>
    <p>SERVER oradb1 options (schema 'CLD', table
      'cust_ord_process_status');</p>
    <p><br>
    </p>
    <p>Now if I run:</p>
    <p>IMPORT FOREIGN SCHEMA CLD FROM SERVER oradb1 INTO local_cld_sch;</p>
    <p>it hangs for a long time and eventually returns this error</p>
    <b><br>
    </b><b>ERROR:  cannot connect to foreign Oracle server</b><b><br>
    </b><b>DETAIL:  ORA-12154:  TNS:could not resolve the connect
      identifier specified</b>
    <p><br>
    </p>
    <br>
    Also If I run this (based on the foreign table above)<br>
    <p>select * from oratab1 limit 10;</p>
    <p><br>
    </p>
    <p>it also hangs for a long time, then I get this error<br>
    </p>
    <p><b><br>
      </b><b>ERROR:  cannot connect</b><b>ion for foreign table
        "oratab1" cannot be established<br>
      </b><b>DETAIL:  ORA-12154:  TNS:could not resolve the connect
        identifier specified</b></p>
    <p><b><br>
      </b></p>
    <p><br>
    </p>
    <p>Thoughts?<br>
    </p>
    <p>Thanks in advance<br>
    </p>
    <p><br>
    </p>
    <br>
    <p><br>
    </p>
  </body>
</html>

--------------0C356377579FE2C2A00D0CAA--