From: | Bricklen Anderson <BAnderson(at)PresiNET(dot)com> |
---|---|
To: | dpandey(at)secf(dot)com |
Cc: | 'PostgreSQL' <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Create connection with Oracle database from Postgres plpgsql |
Date: | 2005-07-05 14:26:15 |
Message-ID: | 42CA9887.7000603@PresiNET.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
_______________________________
From | Date | Subject | |
---|---|---|---|
Next Message | Din Adrian | 2005-07-05 19:10:41 | Re: Help on Procedure running external function |
Previous Message | Bruno Wolff III | 2005-07-05 12:39:48 | Re: Help on Procedure running external function |