Re: Problem With using PERL::DBI in plperlu function

2009/2/23 Tomasz Olszak <tolszak(at)o2(dot)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