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

From: Tomasz Olszak <tolszak(at)o2(dot)pl>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Problem with accesing Oracle from plperlu function when using remote pg client.
Date: 2009-03-16 14:16:07
Message-ID: 7ba36d4a.1bce821f.49be5f27.e7423@o2.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kenneth Marshall 2009-03-16 14:27:29 Re: Problem with accesing Oracle from plperlu function when using remote pg client.
Previous Message Tino Wildenhain 2009-03-16 13:55:29 Re: cross-compiling plpython