OCIEnvCreate failed to create environment handle after installing oracle_fdw

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:

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`

StackExchange Database Administrators Q#14327, answer score: 4