Re: Create connection with Oracle database from Postgres plpgsql

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