pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedoracle_fdw
7+ messages / 4 participants
[nested] [flat]
* oracle_fdw
@ 2012-10-18 20:53 hartrc <rhart2@mt.gov>
0 siblings, 1 reply; 7+ messages in thread
From: hartrc @ 2012-10-18 20:53 UTC (permalink / raw)
To: pgsql-general
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: oracle_fdw
@ 2012-10-19 08:38 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: hartrc <rhart2@mt.gov>
0 siblings, 1 reply; 7+ messages in thread
From: Albe Laurenz @ 2012-10-19 08:38 UTC (permalink / raw)
To: hartrc *EXTERN* <rhart2@mt.gov>; +Cc: oracle-fdw-general@pgfoundry.org; pgsql-general
[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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: oracle_fdw
@ 2012-10-19 13:53 hartrc <rhart2@mt.gov>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 7+ messages in thread
From: hartrc @ 2012-10-19 13:53 UTC (permalink / raw)
To: pgsql-general
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: oracle_fdw
@ 2012-10-22 09:49 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: hartrc <rhart2@mt.gov>
0 siblings, 0 replies; 7+ messages in thread
From: Albe Laurenz @ 2012-10-22 09:49 UTC (permalink / raw)
To: hartrc *EXTERN* <rhart2@mt.gov>; +Cc: oracle-fdw-general@pgfoundry.org; pgsql-general
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Oracle_fdw
@ 2025-04-22 11:28 Yongye Serkfem <yserkfem@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Yongye Serkfem @ 2025-04-22 11:28 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Hello Team!
We are using Postgresql-15, ahd having issues importing foreign schema with
all its objects from an Oracle databse. Here is the error I am getting,
"error connecting to Oracle: OCIEnvCreate failed to create environment
handle: Detail: SQL state: HV00N
I have installed oracle instantclient 19_24
I would appreciate any help getting this resolved.
Regards
Yong Serk
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle_fdw
@ 2025-04-22 11:54 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Yongye Serkfem <yserkfem@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2025-04-22 11:54 UTC (permalink / raw)
To: Yongye Serkfem <yserkfem@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Tue, 2025-04-22 at 07:28 -0400, Yongye Serkfem wrote:
> We are using Postgresql-15, ahd having issues importing foreign schema with all
> its objects from an Oracle databse. Here is the error I am getting,
> "error connecting to Oracle: OCIEnvCreate failed to create environment handle: Detail: SQL state: HV00N
> I have installed oracle instantclient 19_24
> I would appreciate any help getting this resolved.
This error usually indicated that you configured the Oracle Client wrongly.
Usually, that is a question of setting environment variables wrong, but
unfortunately it is a very generic error that Oracle throws when "something
is wrong".
Your best bet is to research all the oracle_fdw issues that reported this
error. Hopefully, one of these cases matches yours:
https://github.com/laurenz/oracle_fdw/issues?q=is%3Aissue%20OCIEnvCreate
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle_fdw
@ 2025-04-22 17:06 Yongye Serkfem <yserkfem@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 7+ messages in thread
From: Yongye Serkfem @ 2025-04-22 17:06 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Thanks, I'll look into it.
On Tue, Apr 22, 2025, 7:54 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> On Tue, 2025-04-22 at 07:28 -0400, Yongye Serkfem wrote:
> > We are using Postgresql-15, ahd having issues importing foreign schema
> with all
> > its objects from an Oracle databse. Here is the error I am getting,
> > "error connecting to Oracle: OCIEnvCreate failed to create environment
> handle: Detail: SQL state: HV00N
> > I have installed oracle instantclient 19_24
> > I would appreciate any help getting this resolved.
>
> This error usually indicated that you configured the Oracle Client wrongly.
> Usually, that is a question of setting environment variables wrong, but
> unfortunately it is a very generic error that Oracle throws when "something
> is wrong".
>
> Your best bet is to research all the oracle_fdw issues that reported this
> error. Hopefully, one of these cases matches yours:
> https://github.com/laurenz/oracle_fdw/issues?q=is%3Aissue%20OCIEnvCreate
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-04-22 17:06 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-18 20:53 oracle_fdw hartrc <rhart2@mt.gov>
2012-10-19 08:38 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2012-10-19 13:53 ` hartrc <rhart2@mt.gov>
2012-10-22 09:49 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2025-04-22 11:28 Oracle_fdw Yongye Serkfem <yserkfem@gmail.com>
2025-04-22 11:54 ` Re: Oracle_fdw Laurenz Albe <laurenz.albe@cybertec.at>
2025-04-22 17:06 ` Re: Oracle_fdw Yongye Serkfem <yserkfem@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox