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
orperl versions are not causes(Of course environment variables are propably set etc.). Oracle Base directory is about
1.6gigabyte 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