From: Robert Treat <xzilla@users.sourceforge.net>
To: pgsql-admin@postgresql.org
Subject: Re: Problem With using PERL::DBI in plperlu function
Date: Mon, 23 Feb 2009 15:30:19 -0500
User-Agent: KMail/1.9.10
Cc: Pawel Socha <pawel.socha@gmail.com>, Tomasz Olszak <tolszak@o2.pl>
References: <57da2175.1ad3af61.49a26c33.ce91@o2.pl>
	<cc4f12900902230324g7a683b96tc0009dd640732837@mail.gmail.com>
In-reply-to: <cc4f12900902230324g7a683b96tc0009dd640732837@mail.gmail.com>
MIME-version: 1.0
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7bit
Content-disposition: inline
Message-id: <200902231530.19579.xzilla@users.sourceforge.net>

On Monday 23 February 2009 06:24:26 Pawel Socha wrote:
> 2009/2/23 Tomasz Olszak <tolszak@o2.pl>:
> > 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.
>
> check $ORA_HOME
> and settings in tnsnames.ora in $ORA_HOME dir.
>
> Or try connect by host,port and SID.
>
> :)
>

There is a bug somewhere in the plperlu <-> oracle dbi stack (most likely in 
the oracle dbd driver, but that just a guess) that prevents these type of 
connections from work from a non-local connection, specifically due to lack 
of environment variable sanity. We spent quite a bit of time trying to figure 
this out a few years ago, but to no avail, so generally my answer on this 
is "you can't do that", but by all means feel free to dig in and post if you 
find a way to make it work (I'm hopeful that newer versions of the software 
involved might work, but haven't had any luck in the combinations I've tested 
so far). 

-- 
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com