Thread: Create connection with Oracle database from Postgres plpgsql function
<div class="Section1"><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">How can we create connection with Oracle database from Postgres plpgsql function and execute some oraclestored procedure?</span></font><p><font face="Verdana" size="2"><span style="font-size:10.0pt">Thanks<br /> Dinesh</span></font><p><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoNormal"><fontface="Times New Roman" size="3"><span style="font-size: 12.0pt"> </span></font></div>
Re: Create connection with Oracle database from Postgres plpgsql function
From
Michael Glaesemann
Date:
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
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. _______________________________