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