[I'll CC the oracle_fdw mailing list where this should be.]
Rob wrote:
> Environment: PostgreSQL 9.1.6
> SLES 11 SP2
> Oracle_fdw 0.9.7
>
> I am trying to implement the use of oracle_fdw.
> So far I have installed an oracle client on my postgres server. I can
> connect to the oracle environment from the postgres server (as
postgres os
> user) by navigating to $ORACLE_HOME/bin then ./sqlplus /nolog
>
> then:
> connect user@instance
>
>
> I have downloaded and run make and make install for the oracle_fdw -
both
> executed successfully. I have created the extension successfully. I
have
> created a foreign server, foreign data wrapper and a foreign table.
>
> When i try a select from the foreign table I get:
>
> ERROR: error connecting to Oracle: OCIEnvCreate failed to create
> environment handle
> DETAIL:
>
> ERROR: error connecting to Oracle: OCIEnvCreate failed to create
environment
> handle
> SQL state: HV00N
>
>
> from what limited info i can find this is most likely due to my
ORACLE_HOME
> environment variable or other environment setting? I have set
ORACLE_HOME in
> postgres user .bash_profile
>
> Where should i set ORACLE_HOME and TNS_ADMIN environment variables? Is
there
> something else I am missing? Does the database require to be restarted
> following any changes to environment variables?
I'd like to know some details about your Oracle installation:
- Which product (Oracle server, Oracle client, Oracle Instant Client,
...)?
- How was Oracle installed (OUI, RPM, zip)?
- Which Oracle version?
Also, can you tell me the "dbserver" option you used in CREATE SERVER?
Now to your problem:
If you can create the extension, the Oracle shared library can be
loaded,
so your library path is fine.
That error is unfortunately a rather generic error that usually means
that something in your environment isn't right.
That may be ORACLE_HOME (if you don't use Instant Client), but could
also be some more obscure environment variable.
Can you send me the environment of the postmaster?
Here's how to get it:
In PostgreSQL:
SELECT pg_backend_pid();
(Let's assume the result is 12345.)
In a second session in the shell:
ps -p12345 -oppid=
(Let's assume the result is 23456.)
As root or PostgreSQL OS user:
cat /proc/23456/environ | xargs -0 -n1
Yours,
Laurenz Albe
