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.