Problem With using PERL::DBI in plperlu function

Поиск
Список
Период
Сортировка
От Tomasz Olszak
Тема Problem With using PERL::DBI in plperlu function
Дата
Msg-id 57da2175.1ad3af61.49a26c33.ce91@o2.pl
обсуждение исходный текст
Ответы Re: Problem With using PERL::DBI in plperlu function
Список 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 по дате отправления:

Предыдущее
От: Nicola Mauri
Дата:
Сообщение: Re: Crash with data corruption under Windows
Следующее
От: Pawel Socha
Дата:
Сообщение: Re: Problem With using PERL::DBI in plperlu function