Обсуждение: Problem with accesing Oracle from plperlu function when using remote pg client.
Problem with accesing Oracle from plperlu function when using remote pg client.
От
Tomasz Olszak
Дата:
Greetings to All!
I've tried to find solution of my problem on other pg mailing lists but without bigger effect.
I have a table A in PG. There is also table A in Oracle.
I want to import specific row from oracle to pg, so i create plperlu function
CREATE OR REPLACE FUNCTION import.ora_a_row(a_id numeric)
RETURNS dok_za AS
$BODY$
In IPL:
create_connection;
select all columns on oracle from table a where id = a_id;
returning tuple;
$BODY$
LANGUAGE 'plperlu' VOLATILE;
then i can use such function in pl/pgsql;
....
DECLARE:
var A%ROWTYPE;
BEGIN;
...
select * into var from import.ora_a_row(100);
END;...
Like you see it's very, very convenient.
And it works, but only when I make "select * from import.ora_a_row(100);" from psql on postgresql server(local client).
When I try to make that select in pgadmin or from remote machine I have tns error:
TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 20
I've tried with different postgresql versions and different perls, and different DBI Oracle packages, so i think pg or perl versions are not causes(Of course environment variables are propably set etc.). Oracle Base directory is about 1.6 gigabyte so I think it's full client(not instant).
When I used PGADMIN 1.6 on postgresql server and left host editline blank(specifying only a pgport) it worked too.
But when I've written "localhost" as host it didn't work (the same with connecting "psql -h localhost -U user database" ).
Anybody ancounter this kind of problem or maybe it's a bug in plperlu?
I'll be grateful for any of Your help.
Regards
Tomasz
I've tried to find solution of my problem on other pg mailing lists but without bigger effect.
I have a table A in PG. There is also table A in Oracle.
I want to import specific row from oracle to pg, so i create plperlu function
CREATE OR REPLACE FUNCTION import.ora_a_row(a_id numeric)
RETURNS dok_za AS
$BODY$
In IPL:
create_connection;
select all columns on oracle from table a where id = a_id;
returning tuple;
$BODY$
LANGUAGE 'plperlu' VOLATILE;
then i can use such function in pl/pgsql;
....
DECLARE:
var A%ROWTYPE;
BEGIN;
...
select * into var from import.ora_a_row(100);
END;...
Like you see it's very, very convenient.
And it works, but only when I make "select * from import.ora_a_row(100);" from psql on postgresql server(local client).
When I try to make that select in pgadmin or from remote machine I have tns error:
TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 20
I've tried with different postgresql versions and different perls, and different DBI Oracle packages, so i think pg or perl versions are not causes(Of course environment variables are propably set etc.). Oracle Base directory is about 1.6 gigabyte so I think it's full client(not instant).
When I used PGADMIN 1.6 on postgresql server and left host editline blank(specifying only a pgport) it worked too.
But when I've written "localhost" as host it didn't work (the same with connecting "psql -h localhost -U user database" ).
Anybody ancounter this kind of problem or maybe it's a bug in plperlu?
I'll be grateful for any of Your help.
Regards
Tomasz
Re: Problem with accesing Oracle from plperlu function when using remote pg client.
От
Kenneth Marshall
Дата:
On Mon, Mar 16, 2009 at 03:16:07PM +0100, Tomasz Olszak wrote: > Greetings to All! > > I've tried to find solution of my problem on other pg mailing lists but without bigger effect. > > I have a table A in PG. There is also table A in Oracle. > I want to import specific row from oracle to pg, so i create plperlu function > > CREATE OR REPLACE FUNCTION import.ora_a_row(a_id numeric) > RETURNS dok_za AS > $BODY$ > > In IPL: > create_connection; > select all columns on oracle from table a where id = a_id; > returning tuple; > > $BODY$ > LANGUAGE 'plperlu' VOLATILE; > > then i can use such function in pl/pgsql; > .... > DECLARE: > var A%ROWTYPE; > BEGIN; > ... > select * into var from import.ora_a_row(100); > END;... > > Like you see it's very, very convenient. > > And it works, but only when I make "select * from import.ora_a_row(100);" from psql?? on postgresql server(local client). > When I try to make that select in pgadmin or from remote machine I have tns error: > > TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 20 > > I've tried with different postgresql versions and different perls, and different DBI Oracle packages, so i think pg orperl versions are not causes(Of course environment variables are propably set etc.). Oracle Base directory is about 1.6gigabyte so I think it's full client(not instant). > > When I used PGADMIN 1.6 on postgresql server and left host editline blank(specifying only a pgport) it worked too. > But when I've written "localhost" as host it didn't work (the same with connecting "psql -h localhost -U user database"). > > Anybody ancounter this kind of problem or maybe it's a bug in plperlu? > > I'll be grateful for any of Your help. > > Regards > > Tomasz > This looks like an ENVIRONMENT variable problem. The server does not run with the same set of settings as your psql program. I think that it will work once you get those issues ironed out. Good luck, Ken