From dpandey@secf.com Tue Jul  5 09:47:09 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 4F34852814
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  5 Jul 2005 06:47:09 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 50960-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  5 Jul 2005 09:47:04 +0000 (GMT)
Received: from secf.com (209-128-104-244.BAYAREA.NET [209.128.104.244])
	by svr1.postgresql.org (Postfix) with ESMTP id 4EFF85280B
	for <pgsql-sql@postgresql.org>; Tue,  5 Jul 2005 06:47:03 -0300 (ADT)
Received: from dinesh by secf.com (MDaemon.PRO.v8.0.2.R)
	with ESMTP id md50001208114.msg
	for <pgsql-sql@postgresql.org>; Tue, 05 Jul 2005 02:47:55 -0700
Reply-To: <dpandey@secf.com>
From: "Dinesh Pandey" <dpandey@secf.com>
To: "'PostgreSQL'" <pgsql-sql@postgresql.org>
Subject: Create connection with Oracle database from Postgres plpgsql function
Date: Tue, 5 Jul 2005 15:10:41 +0530
Organization: Second Foundation India P Ltd.
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_013D_01C58173.B7432420"
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
Thread-Index: AcWBRZtyfioxwXSGToebZmWH/nip8A==
X-Authenticated-Sender: dpandey@secf.com
X-Spam-Processed: mail.secf.com, Tue, 05 Jul 2005 02:47:55 -0700
	(not processed: message from valid local sender)
X-MDRemoteIP: 220.227.32.11
X-Return-Path: dpandey@secf.com
X-MDaemon-Deliver-To: pgsql-sql@postgresql.org
X-MDAV-Processed: mail.secf.com, Tue, 05 Jul 2005 02:47:56 -0700
Message-Id: <20050705094703.4EFF85280B@svr1.postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.406 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, HTML_90_100, HTML_MESSAGE, MSGID_FROM_MTA_ID
X-Spam-Level: *
X-Archive-Number: 200507/23
X-Sequence-Number: 22001

This is a multi-part message in MIME format.

------=_NextPart_000_013D_01C58173.B7432420
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

How can we create connection with Oracle database from Postgres plpgsql
function and execute some oracle stored procedure?

Thanks
Dinesh

 

 


------=_NextPart_000_013D_01C58173.B7432420
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Verdana;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
p
	{mso-margin-top-alt:auto;
	margin-right:0in;
	mso-margin-bottom-alt:auto;
	margin-left:0in;
	font-size:10.0pt;
	font-family:Verdana;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:Verdana;
	color:windowtext;
	font-weight:normal;
	font-style:normal;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>How can we create connection with Oracle database =
from
Postgres plpgsql function and execute some oracle stored =
procedure?<o:p></o:p></span></font></p>

<p><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt'>Thanks<br>
Dinesh<o:p></o:p></span></font></p>

<p><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'><o:p>&nbsp;</o:p></span></font></p>

</div>

</body>

</html>

------=_NextPart_000_013D_01C58173.B7432420--




From grzm@myrealbox.com Tue Jul  5 10:02:21 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 325655286C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  5 Jul 2005 07:02:21 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 49196-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  5 Jul 2005 10:02:16 +0000 (GMT)
Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com
	[192.108.102.143])
	by svr1.postgresql.org (Postfix) with ESMTP id 7FD165280B
	for <pgsql-sql@postgresql.org>; Tue,  5 Jul 2005 07:02:12 -0300 (ADT)
Received: from [172.16.1.204] glaesema [61.197.227.146]
	by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 1.5 $ on
	Linux via secured & encrypted transport (TLS);
	Tue, 05 Jul 2005 04:02:14 -0600
In-Reply-To: <20050705094703.4EFF85280B@svr1.postgresql.org>
References: <20050705094703.4EFF85280B@svr1.postgresql.org>
Mime-Version: 1.0 (Apple Message framework v730)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <C61C5740-FE75-4030-9C5F-E903B7A476A6@myrealbox.com>
Cc: "'PostgreSQL'" <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@myrealbox.com>
Subject: Re: Create connection with Oracle database from Postgres plpgsql
	function
Date: Tue, 5 Jul 2005 19:02:11 +0900
To: <dpandey@secf.com>
X-Mailer: Apple Mail (2.730)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200507/24
X-Sequence-Number: 22002


On Jul 5, 2005, at 6:40 PM, Dinesh Pandey wrote:

> How can we create connection with Oracle database from Postgres  
> plpgsql function and execute some oracle stored procedure?

I don't think it's possible with plpgsql. Try DBI-link.
<http://pgfoundry.org/projects/dbi-link/>

Michael Glaesemann
grzm myrealbox com





From BAnderson@PresiNET.com Tue Jul  5 14:26:18 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3B9075284E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  5 Jul 2005 11:26:18 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 04974-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  5 Jul 2005 14:26:13 +0000 (GMT)
Received: from presinet-main.presinet.com (presinet.com [209.53.156.1])
	by svr1.postgresql.org (Postfix) with ESMTP id 704F752808
	for <pgsql-sql@postgresql.org>; Tue,  5 Jul 2005 11:26:11 -0300 (ADT)
Received: from [10.10.1.151] (BRICK [10.10.1.151]) by
	presinet-main.presinet.com with SMTP (Microsoft Exchange
	Internet Mail Service Version 5.5.2658.3)
	id 3HW05B4Z; Tue, 5 Jul 2005 07:22:05 -0700
Message-ID: <42CA9887.7000603@PresiNET.com>
Date: Tue, 05 Jul 2005 07:26:15 -0700
From: Bricklen Anderson <BAnderson@PresiNET.com>
User-Agent: Debian Thunderbird 1.0.2 (X11/20050602)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: dpandey@secf.com
Cc: 'PostgreSQL' <pgsql-sql@postgresql.org>
Subject: Re: Create connection with Oracle database from Postgres plpgsql
References: <20050705094703.4EFF85280B@svr1.postgresql.org>
In-Reply-To: <20050705094703.4EFF85280B@svr1.postgresql.org>
X-Enigmail-Version: 0.91.0.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.014 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200507/29
X-Sequence-Number: 22007

Dinesh Pandey wrote:
> How can we create connection with Oracle database from Postgres plpgsql
> function and execute some oracle stored procedure?
> 
> Thanks
> Dinesh
You can use perl DBI to access Oracle, providing you have DBI and the plperlu
language installed.

Sample code that may help you get started (lookout for typos):

create or replace function connect_ora() returns void as $$
use DBI;
&main;
sub main {
  my $query="select 1 from dual";
  my $dbh=openDatabase();
  if ($dbh==0) { return; }
  my $sth = $dbh->prepare( $query, {ora_check_sql => 0} ) || elog NOTICE, "Can't
prepare SQL statement: $DBI::errstr\n";
  $sth->execute() || elog ERROR, "Cant execute SQL statement: $DBI::errstr\n";
  my $array_ref = $sth->fetchall_arrayref();
  $sth->finish();
  $dbh->disconnect() || elog WARNING, "Disconnection from db failed\n";
  RETURN;
}
sub openDatabase {
    $dbh =
DBI->connect_cached("dbi:Oracle:host=<host>;sid=<sid>;port=<port>",<ora_username>,<ora_pwd>)
|| elog ERROR, $DBI::errstr;
    $dbh->{RowCacheSize} = 100;
  return $dbh;
}
$$ language plperlu;


Customize as you see fit. YMMV

-- 
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________