DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=Rha12Iwg1gh8hcASjxAzvQbAf6V4NrTMbBUUOr9j/Plf8nudgG4LpclDTep0g88sHQ
	Ft/E7gvd6D2Zv9P+TK4mWH9XWF9Uh98qha7CGakQ5V7gRYiAvFblwnlU60VMeT9jLm/k
	fiY5HQMuIsvNClcZKM8b3wQU0iENjqExVU7N4=
MIME-Version: 1.0
In-Reply-To: <57da2175.1ad3af61.49a26c33.ce91@o2.pl>
References: <57da2175.1ad3af61.49a26c33.ce91@o2.pl>
Date: Mon, 23 Feb 2009 12:24:26 +0100
Message-ID: <cc4f12900902230324g7a683b96tc0009dd640732837@mail.gmail.com>
Subject: Re: Problem With using PERL::DBI in plperlu function
From: Pawel Socha <pawel.socha@gmail.com>
To: Tomasz Olszak <tolszak@o2.pl>
Cc: pgsql-admin@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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.


:)

-- 
Pawel Socha