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.
_______________________________