Thread: Live Link to Oracle?
Sorry if I've hit the wrong group, or if this question has been asked & answered. I couldn't find it in the archives.
My company has a substantial investment in Oracle (one that I'd like to reduce). Mostly Oracle 8.1.7 and lower. There are literally dozens of applications accessing the databases, so porting a complete Oracle databse to PostgreSQL is not in the cards.
I'd like to set up a pgsql database that has server-based links back to an Oracle db. Not replication, but a live link (the data I'm looking for can change rapidly). The pgsql client would see it as a native pgsql table, but the pgsql server would submit the query to Oracle in real-time. Basically, a view of the Oracle data.
At this point, we're not looking to insert/update/delete any Oracle data, nor run stored procs/functions. Just a dang SELECT.
Any ideas?
tia,
Ted.
On Tue, Apr 08, 2003 at 08:37:16AM -0400, Ted Taylor wrote: > I'd like to set up a pgsql database that has server-based links back to > an Oracle db. Not replication, but a live link (the data I'm looking > for can change rapidly). The pgsql client would see it as a native > pgsql table, but the pgsql server would submit the query to Oracle in > real-time. Basically, a view of the Oracle data. There is a dblink in in the postgres source that does this between seperate postgres servers. Perhaps just altering this to deal if oracle servers instead would be possible? Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Attachment
Martijn van Oosterhout wrote: > On Tue, Apr 08, 2003 at 08:37:16AM -0400, Ted Taylor wrote: > >>I'd like to set up a pgsql database that has server-based links back to >>an Oracle db. Not replication, but a live link (the data I'm looking >>for can change rapidly). The pgsql client would see it as a native >>pgsql table, but the pgsql server would submit the query to Oracle in >>real-time. Basically, a view of the Oracle data. > > There is a dblink in in the postgres source that does this between seperate > postgres servers. Perhaps just altering this to deal if oracle servers > instead would be possible? Someone has already modified dblink to use JDBC successfully. He sent me the code, but did not announce anything because he really didn't have time to support it. I'm hoping to have enough time to integrate the jdbclink code into dblink before the freeze for 7.4, but no promises. Joe
Thanks Martijn. Although I consider myself a programmer, I do not consider myself talented enough to modify the source of pgsql. I guess I'm looking for a "canned solution". Ted. -----Original Message----- From: Martijn van Oosterhout [mailto:kleptog@svana.org] Sent: April 8, 2003 7:35 PM To: ted@3shmoos.ca Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Live Link to Oracle? On Tue, Apr 08, 2003 at 08:37:16AM -0400, Ted Taylor wrote: > I'd like to set up a pgsql database that has server-based links back > to an Oracle db. Not replication, but a live link (the data I'm > looking for can change rapidly). The pgsql client would see it as a > native pgsql table, but the pgsql server would submit the query to > Oracle in real-time. Basically, a view of the Oracle data. There is a dblink in in the postgres source that does this between seperate postgres servers. Perhaps just altering this to deal if oracle servers instead would be possible? Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values > or religion but rather by its superiority in applying organized > violence. Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Joe Conway wrote: > Martijn van Oosterhout wrote: > > On Tue, Apr 08, 2003 at 08:37:16AM -0400, Ted Taylor wrote: > > > >>I'd like to set up a pgsql database that has server-based links back to > >>an Oracle db. Not replication, but a live link (the data I'm looking > >>for can change rapidly). The pgsql client would see it as a native > >>pgsql table, but the pgsql server would submit the query to Oracle in > >>real-time. Basically, a view of the Oracle data. > > > > There is a dblink in in the postgres source that does this between seperate > > postgres servers. Perhaps just altering this to deal if oracle servers > > instead would be possible? > > Someone has already modified dblink to use JDBC successfully. He sent me > the code, but did not announce anything because he really didn't have > time to support it. > > I'm hoping to have enough time to integrate the jdbclink code into > dblink before the freeze for 7.4, but no promises. Wow, I can see that having amazing possibilities, which dblink has lots of already! -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073