oracle_fdw

[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

Thanks Laurenz for your post...

Some more info
Oracle Server:Oracle 11g R2 (11.2.0.2.0)
Client: 11.2
Was installed using Oracle Universal Installer

I don't really want to post the full environment of the postmaster but
basically I could see no entry in there for ORACLE_HOME or TNS_ADMIN, should
I?

LD_LIBRARY_PATH=mypostgreshomedirectory/lib

are there any others in particular of interest?

Here is my fdw, server and foreign table specs. I have 'myinstancename'
defined in tnsnames.ora which is in $ORACLE_HOME/NETWORK/ADMIN

CREATE FOREIGN DATA WRAPPER oracle_fdw
HANDLER oracle_fdw_handler
VALIDATOR oracle_fdw_validator;
ALTER FOREIGN DATA WRAPPER oracle_fdw
OWNER TO postgres;

CREATE SERVER myinstancename
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver 'myinstancename');
ALTER SERVER myinstancename
OWNER TO postgres;

CREATE FOREIGN TABLE public.wild_lek
("WL_ID" integer ,
"WL_ALIAS" character varying(50) ,
"WL_AHM_FL" character varying(1) ,
"WL_INACTIVE_FL" character varying(1) ,
"WL_SATELLITE_FL" character varying(20) ,
"WL_LESPPSG_FL" character varying(1) )
SERVER myinstancename
OPTIONS (table 'MYUSER.MYTABLE');
ALTER FOREIGN TABLE 'MYUSER.MYTABLE' OWNER TO postgres;

Rob

--
View this message in context: http://postgresql.1045698.n5.nabble.com/oracle-fdw-tp5728931p5729005.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Rob wrote:

Some more info
Oracle Server:Oracle 11g R2 (11.2.0.2.0)
Client: 11.2
Was installed using Oracle Universal Installer

Ok.

I don't really want to post the full environment of the postmaster but
basically I could see no entry in there for ORACLE_HOME or TNS_ADMIN,

should

I?

Yes, you should see entries for those variables there.

If this is a "regular" client (installed with Universal Installer"),
then a missing ORACLE_HOME environment variable will trigger exactly
the error message you observe.

How do you start the PostgreSQL server? Perhaps .bash_profile
is not read by the startup script's shell. Try .bashrc or
try to define and export it in the startup script itself.

LD_LIBRARY_PATH=mypostgreshomedirectory/lib

are there any others in particular of interest?

Everything that starts with NLS or ORA, for example.

Here is my fdw, server and foreign table specs. I have

'myinstancename'

defined in tnsnames.ora which is in $ORACLE_HOME/NETWORK/ADMIN

[...]

CREATE FOREIGN TABLE public.wild_lek
("WL_ID" integer ,
"WL_ALIAS" character varying(50) ,
"WL_AHM_FL" character varying(1) ,
"WL_INACTIVE_FL" character varying(1) ,
"WL_SATELLITE_FL" character varying(20) ,
"WL_LESPPSG_FL" character varying(1) )
SERVER myinstancename
OPTIONS (table 'MYUSER.MYTABLE');
ALTER FOREIGN TABLE 'MYUSER.MYTABLE' OWNER TO postgres;

You mean ALTER FOREIGN TABLE "public"."wild_lek", right?

Are there any other typos in what you sent?

You don't get to that point yet, but there's a mistake in the table
definition. It should be "OPTIONS (schema 'MYUSER', table 'MYTABLE')".

Yours,
Laurenz Albe