Thread: Live Link to Oracle?

Live Link to Oracle?

From
"Ted Taylor"
Date:
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.

Re: Live Link to Oracle?

From
Martijn van Oosterhout
Date:
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

Re: Live Link to Oracle?

From
Joe Conway
Date:
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


Re: Live Link to Oracle?

From
"Ted Taylor"
Date:
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


Re: Live Link to Oracle?

From
Bruce Momjian
Date:
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