From: Pawel Socha <pawel.socha@gmail.com>
To: Tomasz Olszak <tolszak@o2.pl>
Cc: pgsql-admin@postgresql.org
Subject: Re: Problem With using PERL::DBI in plperlu function
Date: Mon, 23 Feb 2009 12:24:26 +0100
Message-ID: <cc4f12900902230324g7a683b96tc0009dd640732837@mail.gmail.com> (raw)
In-Reply-To: <57da2175.1ad3af61.49a26c33.ce91@o2.pl>
References: <57da2175.1ad3af61.49a26c33.ce91@o2.pl>
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
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: pawel.socha@gmail.com, tolszak@o2.pl
Subject: Re: Problem With using PERL::DBI in plperlu function
In-Reply-To: <cc4f12900902230324g7a683b96tc0009dd640732837@mail.gmail.com>
* 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