1

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

Improve this question
5
  • 1
    Did you use sudo su - postgres ? (the extra hyphen is important) Commented Oct 27, 2017 at 10:10
  • No, just tried it. I checked and saw that all the above files still contain the export statement so nothing really changed. Do I need to do something else here? Thanks for your response though. Commented Oct 27, 2017 at 10:16
  • Sorry, forget it. It is only valid for interactive shells, anyway. Commented Oct 27, 2017 at 10:20
  • My first test would be to login as the postgres user in Linux, then try to run sqlplus or any other Oracle client tool from there. Once you get that working, chances are the Postgres process can successfully create an OCI connection. Commented Oct 27, 2017 at 10:26
  • Thanks, sqlplus works from postgres user, but I have to give the complete library path (/u01/app/oracle/product/11.2.0/xe/bin/sqlplus). It does not find it otherwise. Commented Oct 27, 2017 at 11:22

1 Answer 1

Reset to default
1

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hi Laurenz, thanks a lot for the answer. I raised the same issue in the github repo btw. I can close if you want. So, you mean to say that is the Oracle client issue (or is it the server?). I was thinking may be it is an issue from postgres side as it cannot be the ORACLE_HOME variable in the environment. Also, I have not added the LD_LIBRARY_PATH in the env, but just added the path in /etc/ld.so.conf.d/xxx.conf. Can that be causing the issue? This problem is very similar to the issue I have referenced in the question where you have replied. I followed it, but could not set the ORACLE_HOME.
Your library path is ok, else you would get a different message. It might be ORACLE_HOME. What is certain is that it is a configuration issue with Oracle client, it has nothing to do with the server. Probably also not oracle_fdw's fault, but you never know.
Ok. But as a last ditch effort, can you please tell me how we can provide env variables to postgres? Is there any way to do it?
I don't have an Ubuntu machine at my fingertips, so I can't tell you where the startup script is. To test, start PostgreSQL from the command line with pg_ctl start.
It worked!! Thanks a lot Laurenz. This solves a huge problem for us. I cannot thank you enough.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.