PostgreSql: Set environment variables

I have installed the oracle_fdw plugin for postgreSql and have reached a point where I could create a foreign table. But when I try to query this table, I get the following error:

ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle

I went through the following thread where this exact issue is discussed:

OCIEnvCreate failed to create environment handle. #133

The thing is that I am not able to set the env variable for ORACLE_HOME. Below is the command to check the env and the corresponding output of the running postgres process:

:$ sudo cat /proc/4885/environ | xargs -0 -n 1 echo | sort
LANG=en_IN
PGDATA=/var/lib/postgresql/9.6/main
PG_GRANDPARENT_PID=4880
PGLOCALEDIR=/usr/share/locale
PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
PGSYSCONFDIR=/etc/postgresql-common
PWD=/

I have tried to export this variable in all possible places but to no avail. I referred the following threads:

https://unix.stackexchange.com/questions/117467/how-to-permanently-set-environmental-variables/117470

http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/PostgreSQL_x486_007.htm

Thus I have added env variables in the following files: (after loging in as postgres using sudo su postgres)

~/.bashrc
~/.bash_profile
~/.bash_login
~/.login
~/.profile
/etc/profile

In all of these files I have the following line:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/

Am I missing something here?

PS: I am on Ubuntu 17.10 using Postgres 9.6

Solution

All the locations where you added the variable are only used by shells, so it will only work if you start PostgreSQL from the command line.

It depends on your startup procedure what the best place is: with System V init scripts you can edit the script, with systemd there is a special keyword in the service file. There might be other ways for some Linux distributions.

To address your real problem, you only need ORACLE_HOME if you are not using Instant Client, so I am not certain that that is your problem.

Unfortunately this is the generic error message if Oracle has problems initializing, so it is very hard to diagnose. It can be a bad Oracle environment variable, but I have also seen it if there is a mixup of several Oracle products installed on a single machine, so try to avoid that.

It may be a good idea to trace the backend process with strace so you can see which files Oracle tries to access.

If you have Oracle's source code, a debugger can help (only joking).