Re: oracle_fdw

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