pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCreate connection with Oracle database from Postgres plpgsql function
3+ messages / 3 participants
[nested] [flat]
* Create connection with Oracle database from Postgres plpgsql function
@ 2005-07-05 09:40 Dinesh Pandey <dpandey@secf.com>
0 siblings, 2 replies; 3+ messages in thread
From: Dinesh Pandey @ 2005-07-05 09:40 UTC (permalink / raw)
To: pgsql-sql
How can we create connection with Oracle database from Postgres plpgsql
function and execute some oracle stored procedure?
Thanks
Dinesh
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Create connection with Oracle database from Postgres plpgsql function
@ 2005-07-05 10:02 Michael Glaesemann <grzm@myrealbox.com>
parent: Dinesh Pandey <dpandey@secf.com>
1 sibling, 0 replies; 3+ messages in thread
From: Michael Glaesemann @ 2005-07-05 10:02 UTC (permalink / raw)
To: dpandey@secf.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Create connection with Oracle database from Postgres plpgsql
@ 2005-07-05 14:26 Bricklen Anderson <BAnderson@PresiNET.com>
parent: Dinesh Pandey <dpandey@secf.com>
1 sibling, 0 replies; 3+ messages in thread
From: Bricklen Anderson @ 2005-07-05 14:26 UTC (permalink / raw)
To: dpandey@secf.com; +Cc: pgsql-sql
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.
_______________________________
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-07-05 14:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-05 09:40 Create connection with Oracle database from Postgres plpgsql function Dinesh Pandey <dpandey@secf.com>
2005-07-05 10:02 ` Michael Glaesemann <grzm@myrealbox.com>
2005-07-05 14:26 ` Re: Create connection with Oracle database from Postgres plpgsql Bricklen Anderson <BAnderson@PresiNET.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox