Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E65ED634982 for ; Mon, 23 Feb 2009 05:53:50 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 01578-08-3 for ; Mon, 23 Feb 2009 05:53:43 -0400 (AST) X-Greylist: delayed 00:25:02.782958 by SQLgrey-1.7.6 Received: from tur.go2.pl (tur.go2.pl [193.17.41.50]) by mail.postgresql.org (Postfix) with ESMTP id D08DA63477A for ; Mon, 23 Feb 2009 05:53:41 -0400 (AST) Received: from mailout1.go2.pl (mailout1.go2.pl [193.17.41.11]) by tur.go2.pl (o2.pl Mailer 2.0.1) with ESMTP id 22BDF230888 for ; Mon, 23 Feb 2009 10:28:45 +0100 (CET) Received: from mailout1.go2.pl (unknown [10.0.0.104]) by mailout1.go2.pl (Postfix) with ESMTP id 4CB1A3000B for ; Mon, 23 Feb 2009 10:28:20 +0100 (CET) Received: from o2.pl (unknown [10.0.0.76]) by mailout1.go2.pl (Postfix) with SMTP for ; Mon, 23 Feb 2009 10:28:20 +0100 (CET) Subject: =?UTF-8?Q?Problem_With_using_PERL::DBI_in_plperlu_function?= From: =?UTF-8?Q?Tomasz_Olszak?= To: pgsql-admin@postgresql.org Mime-Version: 1.0 Message-ID: <57da2175.1ad3af61.49a26c33.ce91@o2.pl> Date: Mon, 23 Feb 2009 10:28:19 +0100 X-Originator: 83.18.171.115 Content-Type: multipart/alternative; boundary="==o2.pl-WebMail-1b2f0fdc.6d95b4d6.ce9e==" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200902/192 X-Sequence-Number: 31413 --==o2.pl-WebMail-1b2f0fdc.6d95b4d6.ce9e== Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Greetings! =20 First,=20sorry=20for=20my=20english. =20 Postgresql=208.2=20is=20installed=20on=20Suse.=20 I=20want=20to=20connect=20from=20postgres=20plperlu=20function=20with=20o= racle=20and=20import=20some=20data=20from=20time=20to=20time.=20PGAgent=20= will=20execute=20this=20function=20every=20X=20minutes. =20 Code:I've=20written=20simple=20script=20for=20example: #!/usr/bin/perl use=20strict; use=20DBI; my=20$query=3D"select=201=20from=20dual"; $dbh=20=3DDBI->connect("dbi:Oracle:tns=5Falias","user","pass")||=20 die=20"Database=20connection=20not=20made:=20$DBI::errstr"; my=20$sth=20=3D=20$dbh->prepare(=20$query,=20{ora=5Fcheck=5Fsql=20=3D&= gt;=200}=20); $sth->execute(); my=20$tmp; $sth->bind=5Fcolumns(=20undef,=20\$tmp); if=20(=20$sth->fetch()=20) =20print=20"value=20from=20oracle:$tmp"; else =20print=20"can't=20fetch=20from=20oracle"; =20 I=20execute=20this=20script=20from=20postgres=20user=20on=20linux=20and=20= it's=20working.=20Connection=20with=20oracle=20is=20made=20using=20tnsnam= es.ora=20etc. =20When=20I=20write=20this=20script=20like=20a=20postgresql=20plperlu=20f= unction=20it=20have=20problem=20with=20tnsnames. Code:CREATE=20OR=20REPLACE=20FUNCTION=20connect=5Fora() =20RETURNS=20void=20AS $BODY$ use=20strict; use=20DBI; my=20$query=3D"select=201=20from=20dual"; elog=20WARNING,$ENV{LD=5FLIBRARY=5FPATH}; elog=20WARNING,$ENV{PATH}; elog=20WARNING,$ENV{USER}; elog=20WARNING,$ENV{TNS=5FADMIN}; elog=20WARNING,$ENV{ORACLE=5FSID}; elog=20WARNING,$ENV{ORACLE=5FBASE}; elog=20WARNING,$ENV{ORACLE=5FHOME}; $dbh=20=3DDBI->connect("dbi:Oracle:tns=5Falias","user","pass",{=20Rais= eError=20=3D>=200,=20AutoCommit=20=3D>=200,=20ora=5Fenvhp=3D>=20= 0=20}) ||=20elog=20ERROR,=20$DBI::errstr; my=20$sth=20=3D=20$dbh->prepare(=20$query,=20{ora=5Fcheck=5Fsql=20=3D&= gt;=200}=20); $sth->execute(); my=20$tmp; $sth->bind=5Fcolumns(=20undef,=20\$tmp); if=20(=20$sth->fetch()=20) =20elog=20WARNING,=20"value=20from=20oracle:$tmp"; else =20elog=20ERROR,=20"can't=20fetch=20from=20oracle"; $BODY$ =20LANGUAGE=20'plperlu'=20VOLATILE; =20 When=20i=20execute=20this=20plperlu=20function=20I=20get=20following=20er= ror: =20 NOTICE:=20DBI=20connect('tns=5Ftest','user',...)=20failed:=20ORA-12154:=20= TNS:could=20not=20resolve=20the=20connect=20identifier=20specified=20(DBD= =20ERROR:=20OCIServerAttach)=20at=20line=2019 =20 ERROR:=20error=20from=20Perl=20function:=20ORA-12154:=20TNS:could=20not=20= resolve=20the=20connect=20identifier=20specified=20(DBD=20ERROR:=20OCISer= verAttach)=20at=20line=2019. =20 I'm=20printing=20notices=20(as=20you=20see)=20i=20this=20function=20showi= ng=20values=20of=20environment=20variables.=20They=20are=20the=20same=20a= s=20variables=20in=20postgres=20user=20on=20linux. =20 Any=20idea=20what=20am=20i=20doing=20wrong? =20 I'm=20thankful=20for=20any=20of=20Your=20help. --==o2.pl-WebMail-1b2f0fdc.6d95b4d6.ce9e== Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Greetings!=20=20First,=20sorry=20= for=20my=20english.=20=20Postgresql=208.2=20is=20installe= d=20on=20Suse.=20=20I=20want=20to=20connect=20from=20postgres=20p= lperlu=20function=20with=20oracle=20and=20import=20some=20data=20from=20t= ime=20to=20time.=20PGAgent=20will=20execute=20this=20function=20every=20X= =20minutes.=20 Code: I've=20written=20simple=20script=20for=20example:#!/usr/bin/perl= use=20strict;use=20DBI;my=20$query=3D"sel= ect=201=20from=20dual";$dbh=20=3DDBI->connect("dbi:Oracle:tns=5F= alias","user","pass")||=20die=20"Database=20connection=20not=20ma= de:=20$DBI::errstr";my=20$sth=20=3D=20$dbh->prepare(=20= $query,=20{ora=5Fcheck=5Fsql=20=3D>=200}=20);$sth->execute(= );my=20$tmp;$sth->bind=5Fcolumns(=20undef,=20\$tmp);if=20(=20$sth->fetch()=20)=20=20=20=20print=20"value=20f= rom=20oracle:$tmp";else=20=20=20=20print=20"can't=20fetch= =20from=20oracle"; =20=20I=20execute=20this=20script=20from=20postgres=20use= r=20on=20linux=20and=20it's=20working.=20Connection=20with=20oracle=20is=20= made=20using=20tnsnames.ora=20etc.=20When=20I=20write=20this=20sc= ript=20like=20a=20postgresql=20plperlu=20function=20it=20have=20problem=20= with=20tnsnames. Code: CREATE=20OR=20REPLACE=20FUNCTION=20connect=5Fora()=20=20RETURNS=20= void=20AS$BODY$use=20strict;use=20DBI;my=20$query=3D"select=201=20from=20dual";elog=20WARN= ING,$ENV{LD=5FLIBRARY=5FPATH};elog=20WARNING,$ENV{PATH};e= log=20WARNING,$ENV{USER};elog=20WARNING,$ENV{TNS=5FADMIN};elog=20WARNING,$ENV{ORACLE=5FSID};elog=20WARNING,$ENV{ORACLE=5FB= ASE};elog=20WARNING,$ENV{ORACLE=5FHOME};$dbh=20=3DDBI->= ;connect("dbi:Oracle:tns=5Falias","user","pass",{=20RaiseError=20=3D>=20= 0,=20AutoCommit=20=3D>=200,=20ora=5Fenvhp=3D>=200=20})||=20= elog=20=20=20ERROR,=20$DBI::errstr;my=20$sth=20=3D=20$dbh->pre= pare(=20$query,=20{ora=5Fcheck=5Fsql=20=3D>=200}=20);$sth->= execute();my=20$tmp;$sth->bind=5Fcolumns(=20undef,=20\= $tmp);if=20(=20$sth->fetch()=20)=20=20=20=20elog=20WAR= NING,=20"value=20from=20oracle:$tmp";else=20=20=20=20elog= =20ERROR,=20"can't=20fetch=20from=20oracle";$BODY$=20=20L= ANGUAGE=20'plperlu'=20VOLATILE; =20=20When=20i=20execute=20this=20plperlu=20function=20I=20= get=20following=20error:=20=20NOTICE:=20DBI=20connect('tn= s=5Ftest','user',...)=20failed:=20ORA-12154:=20TNS:could=20not=20resolve=20= the=20connect=20identifier=20specified=20(DBD=20ERROR:=20OCIServerAttach)= =20at=20line=2019=20=20=20ERROR:=20error=20from=20= Perl=20function:=20ORA-12154:=20TNS:could=20not=20resolve=20the=20connect= =20identifier=20specified=20(DBD=20ERROR:=20OCIServerAttach)=20at=20line=20= 19.=20=20I'm=20printing=20notices=20(as=20you=20see)=20i=20= this=20function=20showing=20values=20of=20environment=20variables.=20They= =20are=20the=20same=20as=20variables=20in=20postgres=20user=20on=20linux.= =20=20Any=20idea=20what=20am=20i=20doing=20wrong?= =20=20I'm=20thankful=20for=20any=20of=20Your=20help. --==o2.pl-WebMail-1b2f0fdc.6d95b4d6.ce9e==--