Postgres Pro
Downloads
Home   >   mailing lists

Thread: oracle_fdw

oracle_fdw

From
hartrc
Date:
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?

Thank you
Rob





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

Re: oracle_fdw

From
"Albe Laurenz"
Date:
[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

Re: oracle_fdw

From
hartrc
Date:
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.

Re: oracle_fdw

From
"Albe Laurenz"
Date:
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