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;
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.