PostgreSQL Oracle FDW – OCIEnvCreate Failed to Create Environment Handle

oracleoracle-fdwpostgresql

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:

Best Answer

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

Related Solutions

Create Oracle ASM Disks Without Installing Oracle

Mat is right. ASMlib is only one way to created candidate disks for ASM. I would recommend you use udev as detailed here.

Sql-server – Oracle database link via ODBC to SQL Server does not recover automatically

Step by step:

There is no way to test a dblink other than select a dummy over it
A network timeout affects only the running transaction that is currently the dblink. There is no recorded state about the dblink, there is no status like ACTIVE or BROKEN. DBLink creation simply instruct the DB about remote host, port, tns, user and password. Hence drop and recreate the dblink does not affect or change the network or ODBC problem. A pratical case is: select sysdate from dual@dblink. A firewall is in place between both databases. Source DB opens the connection, retrieve data and leave connection opened. After 15mins the firewall drop the connection. At minute 20 another user performs the same query. The database will reopen the connection.
The best automatic way should be to query a table; this only if we are experiecing a simple network issue.

Apart from this I understand that you get the connection dropped and you have to way to re-establish it without drop and recreate the database. Metalink (formerly My Oracle Support) suggest to check if HS=OK is correctly set in the tnsnames.ora. Attention: the HS=OK argument needs to be outside of the CONNECT_DATA portion of the entry. Here an example:

MSSQL =
(DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS =
              (PROTOCOL = tcp)
              (HOST = 192.168.36.18)
              (PORT = 1522)
             )
        )
   (CONNECT_DATA =
        (SID = MSSQL)
   )
   (HS=OK)
)

Moreover I suggest you to check (and eventually post) logs on both sides. You can also increase the level of HS_FDS_TRACE_LEVEL parameter. In addition MOS reports some issue with DG4ODBC and suggest to debug them (and fill a bug if necessary) using trace on the ODBC layer.