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 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 client I have tns error:
TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 20
I've tried with different pg servers and different perls, and differen DBI Oracle packages, so i think pg or perl versions are not causes(Of course environment variables are propably set etc.).
When I used PGADMIN 1.6 on postgresql server and left host editline blank it worked too.
Anybody ancounter this kind of problem?
I'll be grateful for any of Your help.
Regards
Tomasz