Re: Problem with accesing Oracle from plperlu function when using remote pg client.

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