Thank you for quick answer.
I understand, but when I print from plperlu function notices with result of `env` they're the same in both cases (from remote and local client).
So it looks like that plperlu function is executing from remote and local clients with the same set of environment variable.
So I don't have a clue how can I iron out this issue.
Dnia 16 marca 2009 15:27 Kenneth Marshall <ktm(at)rice(dot)edu> napisał(a):
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