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