pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Tomasz Olszak <tolszak@o2.pl>
To: pgsql-admin@postgresql.org
Subject: Problem With using PERL::DBI in plperlu function
Date: Mon, 23 Feb 2009 10:28:19 +0100
Message-ID: <57da2175.1ad3af61.49a26c33.ce91@o2.pl> (raw)

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-&gt;connect("dbi:Oracle:tns_alias","user","pass")|| 
die "Database connection not made: $DBI::errstr";
my $sth = $dbh-&gt;prepare( $query, {ora_check_sql =&gt; 0} );
$sth-&gt;execute();
my $tmp;
$sth-&gt;bind_columns( undef, \$tmp);
if ( $sth-&gt;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-&gt;connect("dbi:Oracle:tns_alias","user","pass",{ RaiseError =&gt; 0, AutoCommit =&gt; 0, ora_envhp=&gt; 0 })
|| elog ERROR, $DBI::errstr;
my $sth = $dbh-&gt;prepare( $query, {ora_check_sql =&gt; 0} );
$sth-&gt;execute();
my $tmp;
$sth-&gt;bind_columns( undef, \$tmp);
if ( $sth-&gt;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.

view thread (6+ messages)  latest in thread

Message-ID: <57da2175.1ad3af61.49a26c33.ce91@o2.pl>
Permalink:  ../57da2175.1ad3af61.49a26c33.ce91@o2.pl/
Also on:    postgresql.org/message-id/57da2175.1ad3af61.49a26c33.ce91@o2.pl

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: tolszak@o2.pl
  Subject: Re: Problem With using PERL::DBI in plperlu function
  In-Reply-To: <57da2175.1ad3af61.49a26c33.ce91@o2.pl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox