OCIEnvCreate failed to create environment handle after installing oracle_fdw

Question

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:

Sathyajith Bhat · Accepted Answer · 2012-05-09 10:42:38Z

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

Sometimes When this error occur, we need restart PostgreSQL Server. — francs
– francs, Commented Jul 18, 2012 at 1:03

jgrocha · Accepted Answer · 2012-05-08 13:48:51Z

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.

Gauthier Boaglio · Accepted Answer · 2014-01-21 03:55:16Z

1

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.

answered Jan 21, 2014 at 3:55

Gauthier Boaglio

1113 bronze badges

Add a comment |

Stack Exchange Network

OCIEnvCreate failed to create environment handle after installing oracle_fdw

3 Answers 3

Oracle

Postgresql Path

tnsnames.ora:

Your Answer

Linked

Hot Network Questions

OCIEnvCreate failed to create environment handle after installing oracle_fdw

3 Answers 3

Oracle

Postgresql Path

tnsnames.ora:

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions