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 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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
