Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have installed oracle_fdw successfully. After I create extension oracle_fdw and foreign table it shows the following error. Is this a server configuration problem?

--env
Oracle :    10.2.0.1
PostgreSQL:  9.1.3


--create foreign server
skytf=#   CREATE SERVER oracle_srv 
skytf-#   FOREIGN DATA WRAPPER oracle_fdw 
skytf-#   OPTIONS (dbserver '//192.168.1.30:1521/MANUA');
CREATE SERVER

skytf=# grant usage on foreign server oracle_srv to skytf;
GRANT

--create mapping user
skytf=# CREATE USER MAPPING FOR skytf
skytf-# SERVER oracle_srv 
skytf-# OPTIONS (user 'read_only', password 'read_only');
CREATE USER MAPPING

--create foreign table
skytf=# CREATE FOREIGN TABLE ft_test_1 (
skytf(#  id       integer,
skytf(#  name     character varying(20) 
skytf(#  ) SERVER oracle_srv
skytf-# OPTIONS (schema 'ocp', table 'test_1');
CREATE FOREIGN TABLE


skytf=# \c skytf skytf
skytf=> select * from ft_test_1;
ERROR:  error connecting to Oracle: OCIEnvCreate failed to create environment handle
DETAIL:  
share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

You have to set the environment variables LD_LIBRARY_PATH and ORACLE_HOME for the user postgresql. Then you have to set the service to conncet in the file tnsname.ora, the you have to create a foreign server with dbserver=Service (you should have defined the Service in the file `tnsname.ora

I send you my files configurations in Debian:

I added to /etc/profile (apply for all system user) but you can use .bashrc or another for only one user.

Oracle

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client
export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
PATH=$PATH:$ORACLE_HOME/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin

Postgresql Path

PATH=$PATH:/usr/local/pgsql/bin
export PATH
MANPATH=$MANPATH:/usr/local/pgsql/man
export MANPATH

The variable TNS_ADMIN will be a directory where you install a tnsnames.ora

tnsnames.ora:

ORA11 =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA11)
 )
)

Then create a foreign server for oracle_fdw with option dbserver=ORA11

share|improve this answer
    
Sometimes When this error occur, we need restart PostgreSQL Server. –  francs Jul 18 '12 at 1:03
add comment

I was able to install oracle_fdw, between Postgresql and Oracle. I've installed Oracle Instant client on the Linux machine.

On the Postgresql server, I've installed Oracle Client, and configured it to connect to the Oracle database. sqlplus name/password@ORCL is working on the command line.

My tnsnames.ora is:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.148.51.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

I've copied tnsnames.ora to $ORACLE_HOME/network/admin/tnsnames.ora

My environment variables are:

export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$ORACLE_HOME/bin:/usr/pgsql-9.1/bin/:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

On the postgresql init script, /etc/init.d/postgresql, I've also set the variables:

export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin

I've also changed the file /etc/ld.so.conf.d/postgresql-9.1-libs.conf

$ cat /etc/ld.so.conf.d/postgresql-9.1-libs.conf
/usr/pgsql-9.1/lib/
/usr/lib/oracle/11.2/client64/lib/

$ sudo ldconfig
$ sudo /etc/init.d/postgresql restart

To use oracle_fdw, I used:

$ psql ide

ide=# CREATE EXTENSION oracle_fdw;
ide=# CREATE SERVER medidata FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.148.51.2:1521/ORCL');

I already have a small table in Oracle called 'zonas'. To use 'zonas' in Postgresql, I've done:

ide=# CREATE FOREIGN TABLE zonas (
    COD_ZONA text,
    ZONA    text,
    RESPONSAVEL       numeric
    ) 
SERVER medidata
OPTIONS (table 'zonas');

ide=# select * from zonas;
 cod_zona | zona | responsavel 
----------+------+-------------
 2        | 2    |           2
 1        | 1    |           1
(2 rows)

I hope it helps.

share|improve this answer
add comment

If this can be of any help for someone else (this answer isn't directly related to the OP question), I also experienced this exact same message while trying to connect PostgrSQL 64-bit (and its plugged-in 64-bit release of oracle_fdw) to a 32-bit version of Oracle Database. Where was my mind? But other people could have made the same mistake...

Here is a more complete documentation about that: wiki page.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.