Обсуждение: Problem With using PERL::DBI in plperlu function

Поиск

Список

Период

Сортировка

Problem With using PERL::DBI in plperlu function

От

Tomasz Olszak

Дата:

23 февраля 2009 г., 08:53:51

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.

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

От

Pawel Socha

Дата:

23 февраля 2009 г., 10:24:33

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

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

От

Robert Treat

Дата:

23 февраля 2009 г., 19:30:45

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

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

От

Tomasz Olszak

Дата:

24 февраля 2009 г., 07:32:29

Than you for Your answer Robert.

I've trying a lot of different configurations and the same perl body that works in OS perl script but doesn't work in plperlu function.

I think it's a little bit confusing that almost all examples in Internet showing creating connection from postgres to oracle the way I did it.

It seems that nobody tried to connect (from plperlu function) with external database(not installed on localhost and not in local network).

Version Of perl that i'm using is 5.8.8, postgresql 8.2.9, oracle 10.2 client and DBI version is 1.52.

I'll try to install latest DBI and perl.
If it's plperlu problem i will not try with latest postgresql because databases and application i'm using i too big to change source code of pg functions and application in few days to work with 8.3.* databases.

If I find any solutions i will write an answer.

Greetings

Tomek

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

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

От

Tomasz Olszak

Дата:

26 февраля 2009 г., 12:56:54

Mónica, thanks for reply

I didn't install client, and i don't know how to check if it's instant or complete. What I can do is:
[CODE]
TOlszak@heman:/opt/oracle> sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 26 14:40:01 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.
[/CODE]

and size $ORACLE_BASE catalog is 1.6 GB.

I suppose it's complete then.

Dnia 26 lutego 2009 13:22 Mónica Gamarra <monica.gamarra@gmail.com> napisał(a):

Tomasz
which oracle client did yoou install?
we found that dbi don't work with instant client and it does with the complete client.

2009/2/24 Tomasz Olszak <tolszak@o2.pl>
Than you for Your answer Robert.

I've trying a lot of different configurations and the same perl body that works in OS perl script but doesn't work in plperlu function.

I think it's a little bit confusing that almost all examples in Internet showing creating connection from postgres to oracle the way I did it.

It seems that nobody tried to connect (from plperlu function) with external database(not installed on localhost and not in local network).

Version Of perl that i'm using is 5.8.8, postgresql 8.2.9, oracle 10.2 client and DBI version is 1.52.

I'll try to install latest DBI and perl.
If it's plperlu problem i will not try with latest postgresql because databases and application i'm using i too big to change source code of pg functions and application in few days to work with 8.3.* databases.

If I find any solutions i will write an answer.

Greetings

Tomek

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

--
............................................
Mónica Gamarra Barrios
monica.gamarra@gmail.com

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Problem With using PERL::DBI in plperlu function

Problem With using PERL::DBI in plperlu function

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

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

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

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