Problem With using PERL::DBI in plperlu function

[Tomasz Olszak <tolszak@xxxxx>]

Greetings!

First, sorry for my english.

Postgresql 8.2 is installed on Suse.
I want to connect from postgres plperlu function with oracle and import some data from time to time. PGAgent will execute this function every X minutes.

Code:

I've written simple script for example:
#!/usr/bin/perl

use strict;
use DBI;
my $query="select 1 from dual";
$dbh =DBI->connect("dbi:Oracle:tns_alias","user","pass")|| 
die "Database connection not made: $DBI::errstr";

my $sth = $dbh->prepare( $query, {ora_check_sql => 0} );
$sth->execute();
my $tmp;
$sth->bind_columns( undef, \$tmp);
if ( $sth->fetch() )
    print "value from oracle:$tmp";
else
    print "can't fetch from oracle";

I execute this script from postgres user on linux and it's working. Connection with oracle is made using tnsnames.ora etc.
When I write this script like a postgresql plperlu function it have problem with tnsnames.

Code:

CREATE OR REPLACE FUNCTION connect_ora()
  RETURNS void AS
$BODY$
use strict;
use DBI;

my $query="select 1 from dual";

elog WARNING,$ENV{LD_LIBRARY_PATH};
elog WARNING,$ENV{PATH};
elog WARNING,$ENV{USER};
elog WARNING,$ENV{TNS_ADMIN};
elog WARNING,$ENV{ORACLE_SID};
elog WARNING,$ENV{ORACLE_BASE};
elog WARNING,$ENV{ORACLE_HOME};
$dbh =DBI->connect("dbi:Oracle:tns_alias","user","pass",{ RaiseError => 0, AutoCommit => 0, ora_envhp=> 0 })
|| elog   ERROR, $DBI::errstr;
my $sth = $dbh->prepare( $query, {ora_check_sql => 0} );
$sth->execute();
my $tmp;
$sth->bind_columns( undef, \$tmp);
if ( $sth->fetch() )
    elog WARNING, "value from oracle:$tmp";
else
    elog ERROR, "can't fetch from oracle";
$BODY$
  LANGUAGE 'plperlu' VOLATILE;

When i execute this plperlu function I get following error:

NOTICE: DBI connect('tns_test','user',...) failed: ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 19


ERROR: error from Perl function: ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 19.

I'm printing notices (as you see) i this function showing values of environment variables. They are the same as variables in postgres user on linux.

Any idea what am i doing wrong?

I'm thankful for any of Your help.