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/
Show quoted text
"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
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/
Show quoted text
"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
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
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