OCIEnvCreate failed to create environment handle after installing oracle_fdw

3 votes
1 answer
1768 views
                          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 : https://dba.stackexchange.com/questions/14327/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


                        
Asked by Mohammad (31 rep)
Mar 2, 2015, 01:24 PM
Last activity: Sep 4, 2019, 01:27 PM
OCIEnvCreate failed to create environment handle after installing oracle_fdw

Related Questions
