Обсуждение: oracle_fdw
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.
[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