Problem With using PERL::DBI in plperlu function - Mailing list pgsql-admin

From Tomasz Olszak
Subject Problem With using PERL::DBI in plperlu function
Date
Msg-id 57da2175.1ad3af61.49a26c33.ce91@o2.pl
Whole thread Raw
Responses Re: Problem With using PERL::DBI in plperlu function  (Pawel Socha <pawel.socha@gmail.com>)
List pgsql-admin
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.

pgsql-admin by date:

Previous
From: Nicola Mauri
Date:
Subject: Re: Crash with data corruption under Windows
Next
From: Pawel Socha
Date:
Subject: Re: Problem With using PERL::DBI in plperlu function