Thread: Oracle / Postgres Interface

Oracle / Postgres Interface

From
"Shah, Sameer"
Date:

Hi All,

 

I would appreciate any input on this dilemma:

 

  1. I have DataBase-A which is Oracle on Server A with FunctionA (PLSQL)
  2. I have DataBase-B which is Postgress on Server B with FunctionB (PgSQL)
  3. I need to call FunctionA from FunctionB

 

I don’t mind rewriting PgSQL to some other language in Postgress if need be.

 

Can I do this? If Yes, How?

 

Please reply as soon as possible.

 

Thanks,

Sameer

 

Re: Oracle / Postgres Interface

From
Doug McNaught
Date:
"Shah, Sameer" <SShah@vicr.com> writes:

>     1. I have DataBase-A which is Oracle on Server A with FunctionA
>        (PLSQL)
>     2. I have DataBase-B which is Postgress on Server B with FunctionB
>        (PgSQL)
>     3. I need to call FunctionA from FunctionB

As far as I know, the on'y way to do this is to write a Postgres
function (function B) in C that links with the Oracle client libraries,
connects to Oracle, and calls your function A.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Re: Oracle / Postgres Interface

From
Greg Stark
Date:
Doug McNaught <doug@mcnaught.org> writes:

> "Shah, Sameer" <SShah@vicr.com> writes:
>
> >     1. I have DataBase-A which is Oracle on Server A with FunctionA
> >        (PLSQL)
> >     2. I have DataBase-B which is Postgress on Server B with FunctionB
> >        (PgSQL)
> >     3. I need to call FunctionA from FunctionB
>
> As far as I know, the on'y way to do this is to write a Postgres
> function (function B) in C that links with the Oracle client libraries,
> connects to Oracle, and calls your function A.

If performance isn't an issue it might be faster to get going using a PerlU
function in Postgres that uses DBD::Oracle to connect to Oracle.

It's the same thing but you don't have to learn two new C APIs. If you already
know how to use Pro*C and the postgres C API then there's probably no benefit.

--
greg