OCIEnvCreate failed to create environment handle after installing oracle_fdw
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
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.