Postgresql – OCIEnvCreate failed to create environment handle after installing oracle_fdw

oracleoracle-fdwpostgresql

I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw.
but when use this extenstion i get error

OCIEnvCreate failed to create environment handle after installing oracle_fdw.

my roadmap:

  • install postgresql 9.3 from postgres rpm.
  • install oracle instant client 11.2g with rpm files from oracle site.
  • install oracle-instant client11.2-basic & oracle-instantclient11.2-sqlplus &
    oracle-instantclient11.2-devel.

I use this question : OCIEnvCreate failed to create environment handle after installing oracle_fdw

my variables : 

/etc/profile :
export PG_HOME=/usr/pgsql-9.3
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$ORACLE_HOME/bin:$PG_HOME/bin/:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME:$PG_HOME/lib:$LD_LIBRARY_PATH

tnsnames.ora :

I created /usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora:

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

When I use the sqlplus command line, I can connect using sqlplus user/pass@BEH and can select from tables.

Using psql in my Postgres database:

CREATE EXTENSION oracle_fdw; 
      resutlt : Query returned successfully.

CREATE SERVER beh FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'AMI_BEH');
   OR
   CREATE SERVER beh FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//172.18.102.107:1521/ORCL');  
      result : Query returned successfully.

CREATE USER MAPPING FOR postgres SERVER beh OPTIONS (user 'ORACLE_USER',password 'ORACLE_PASS');
      result : Query returned successfully.

CREATE FOREIGN TABLE bl
   ("OLD_DYDJ" character varying(20) ,
    "DYDJ" character varying(20) ,
    "MC" character varying(128) )
   SERVER beh
   OPTIONS (table 'DYDJ_DY');
      result : Query returned successfully.

SELECT * FROM bl;
      result : error connecting to Oracle: OCIEnvCreate failed to create environment handle.

I checked that I can use sqlplus as the postgres user (su - postgres) and I can connect to oracle with the sqlplus command.

I found this link http://permalink.gmane.org/gmane.comp.db.postgresql.general/166914 , so I checked:

SELECT pg_backend_pid(); result : 3854
ps -p3854 -oppid=        result : 1902
cat /proc/1902/environ | xargs -0 -n1
   result : 
      PG_GRANDPARENT_PID=1
      USER=postgres
      PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
      PWD=/
      PGLOCALEDIR=/usr/pgsql-9.3/share/locale
      LANG=en_US.UTF-8
      PGSYSCONFDIR=/etc/sysconfig/pgsql
      SHLVL=1
      HOME=/var/lib/pgsql
      LOGNAME=postgres
      PGDATA=/var/lib/pgsql/9.3/data
      _=/usr/pgsql-9.3/bin/postgres

Best Answer

If you install from an rpm then it's installed as a service.

If you start postgresql from a custom script as user postgres then the process will catch the environment variables but if you start postgresql as a service it won't.

You should look for how to pass environment variables to systemd (or sysv) services.

I used this example for systemd services. CentOS 7 uses systemd.

In /etc/systemd/system create postgresql.service.d directory and place inside a file orafdw.conf with your environment variables and restart (not sure about only reload). Make sure the directory name match your postgresql service name.

Something like...

[Service]
Environment=ORACLE_HOME=/path/to/home
Environment=TNS_ADMIN=/path/to/tnsadmin
...
(All the variables you want)

Regards,

Walter

Related Question