Linked Server to Oracle

[Sudhesh Nayak]

Hi,
I have an Oracle (8.1) & a SQL Server 2000 database with
Production data. There are situations when I need data from both the
databases. My first choice was to link Oracle to SQL and run DTS
overnight. But this would have a 1 day latency not to mention the time
it would take.

1. Has any one tried real time access via Linked server to Oracle?
How good is the performance?

2. The Oracle db is fairly big, so I'm kinda not in favor of
copying the whole thing over into SQL overnight. Is there an easier
way to just get only the changed records from Oracle?

3. Is there a better solution to this?

4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamically
change the SQL that you pass it? e.g. can the query accept a
parameter?

Thanks in advance
Sudhesh

[Daniel Morgan]

Re: Linked Server to Oracle

Sudhesh Nayak wrote:
[color=blue]
> Hi,
> I have an Oracle (8.1) & a SQL Server 2000 database with
> Production data. There are situations when I need data from both the
> databases. My first choice was to link Oracle to SQL and run DTS
> overnight. But this would have a 1 day latency not to mention the time
> it would take.
>
> 1. Has any one tried real time access via Linked server to Oracle?
> How good is the performance?
>
> 2. The Oracle db is fairly big, so I'm kinda not in favor of
> copying the whole thing over into SQL overnight. Is there an easier
> way to just get only the changed records from Oracle?
>
> 3. Is there a better solution to this?
>
> 4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamically
> change the SQL that you pass it? e.g. can the query accept a
> parameter?
>
> Thanks in advance
> Sudhesh[/color]

Why not use an Oracle database link for real-time access to SQL Server?
Also look at Oracle's SQL Server Transparent Gateway for accessing SQL
Server with PL/SQL.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Sudhesh Nayak]

Re: Linked Server to Oracle

I need to go from SQL to Oracle as the Oracle db is owned by a
different group within the company... We have more (better) access to
the SQL box...

Thanks for the suggestion though
Sudhesh

Daniel Morgan <damorgan@x.was hington.edu> wrote in message news:<107272337 1.798845@yasure >...[color=blue]
> Sudhesh Nayak wrote:
>[color=green]
> > Hi,
> > I have an Oracle (8.1) & a SQL Server 2000 database with
> > Production data. There are situations when I need data from both the
> > databases. My first choice was to link Oracle to SQL and run DTS
> > overnight. But this would have a 1 day latency not to mention the time
> > it would take.
> >
> > 1. Has any one tried real time access via Linked server to Oracle?
> > How good is the performance?
> >
> > 2. The Oracle db is fairly big, so I'm kinda not in favor of
> > copying the whole thing over into SQL overnight. Is there an easier
> > way to just get only the changed records from Oracle?
> >
> > 3. Is there a better solution to this?
> >
> > 4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamically
> > change the SQL that you pass it? e.g. can the query accept a
> > parameter?
> >
> > Thanks in advance
> > Sudhesh[/color]
>
> Why not use an Oracle database link for real-time access to SQL Server?
> Also look at Oracle's SQL Server Transparent Gateway for accessing SQL
> Server with PL/SQL.[/color]

[Daniel Morgan]

Re: Linked Server to Oracle

Sudhesh Nayak wrote:[color=blue]
> I need to go from SQL to Oracle as the Oracle db is owned by a
> different group within the company... We have more (better) access to
> the SQL box...
>
> Thanks for the suggestion though
> Sudhesh[/color]

Then I'd suggest you talk to the Oracle group. They will likely not
allow you to access their database by whatever means you choose but
their DBAs will likely have strong conditions they will want to place
you your activities: I know I would.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Serge Rielau]

Re: Linked Server to Oracle

Can Oracle 8 act as an OLE DB provider to SQL Server as consumer?
In this case there is really no difference between using SQL Server as a
base through OLE DB vs. Oracle through transparent gateway, other than
that of control.

Just my two cents
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Daniel Morgan]

Re: Linked Server to Oracle

Serge Rielau wrote:[color=blue]
> Can Oracle 8 act as an OLE DB provider to SQL Server as consumer?
> In this case there is really no difference between using SQL Server as a
> base through OLE DB vs. Oracle through transparent gateway, other than
> that of control.
>
> Just my two cents
> Serge[/color]

Difference is that with the Transparent Gateway and Oracle Developer
could issue PL/SQL against the SQL Server database rather than Transact
SQL. Cuts down on a lot of training and makes it possible to write
single SQL statements that simultaneously hit both back-ends.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Serge Rielau]

Re: Linked Server to Oracle

Daniel,

It seems the original poster is sitting on SQL Server. The Oracle DBMS
belongs to "another Group". So the argumnet of traing goes teh other way
around.
I don't know SQL Server well enough, but I'd be surprised if they don't
support OLE Table Functions which then, of course, allows T-SQL to hit
multiple sources at the same time, just like transparent gateway does.
The really interesting question is: How much optimization does
transparent gateway provide (in Oracle 8)? Only if it supports
distributed optimization with subquery pushdown to SQL Server will there
be any conceptual difference since OLE does not provide such
capabilities. Judging by the whitepapers available so far it seems some
of this heterogeneous optimization is coming in Oracle 10g, but that
isn't what the poster has.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Daniel Morgan]

Re: Linked Server to Oracle

Serge Rielau wrote:[color=blue]
> Daniel,
>
> It seems the original poster is sitting on SQL Server. The Oracle DBMS
> belongs to "another Group". So the argumnet of traing goes teh other way
> around.
> I don't know SQL Server well enough, but I'd be surprised if they don't
> support OLE Table Functions which then, of course, allows T-SQL to hit
> multiple sources at the same time, just like transparent gateway does.
> The really interesting question is: How much optimization does
> transparent gateway provide (in Oracle 8)? Only if it supports
> distributed optimization with subquery pushdown to SQL Server will there
> be any conceptual difference since OLE does not provide such
> capabilities. Judging by the whitepapers available so far it seems some
> of this heterogeneous optimization is coming in Oracle 10g, but that
> isn't what the poster has.
>
> Cheers
> Serge[/color]

I'm not sure why you specify Oracle 8 as is it over a decade old. Could
be 8i, could be 9i, and it is essential the OP find out. If the SQL must
be executed on the SQL Server side, as has since been indiated,
Transparent Gateway is out. My suggestion is that the OP work out the
solution with the Oracle DBA responsible for the database. It is likely
they will not say "Yes" to whatever is proposed but will have very
specific ideas and concerns.

You are correct about 10g. I've been working with it for months and it
is quite a different animal.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Serge Rielau]

Re: Linked Server to Oracle

Here is how the thread started....
"I have an Oracle (8.1) & a SQL Server 2000 database with Production data."

Larry won't give me Oracle 10g early I'm affraid. But that's OK :-)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Daniel Morgan]

Re: Linked Server to Oracle

Serge Rielau wrote:
[color=blue]
> Here is how the thread started....
> "I have an Oracle (8.1) & a SQL Server 2000 database with Production data."
>
> Larry won't give me Oracle 10g early I'm affraid. But that's OK :-)
>
> Cheers
> Serge[/color]

You can get the 10g JDeveloper now if you want.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Paul]

Re: Linked Server to Oracle


Sudhesh@mail.co m says...

[color=blue]
> I have an Oracle (8.1) & a SQL Server 2000 database with
> Production data. There are situations when I need data from both the
> databases. My first choice was to link Oracle to SQL and run DTS
> overnight. But this would have a 1 day latency not to mention the time
> it would take.[/color]


OK, what I would do here is ask the Oracle people for read only access
to their db - I would also request that they time-stamp their records
(or at least the ones you're interested in).

You can then write a programme in the language of your choice to act as
a service every night to go and get the records in the Oracle database
that are new - you could even run this service every 5 minutes or so if
the Oracle people let you - the advantage here is that you will only be
drawing down relatively small amounts of data if you do it regularly.

[color=blue]
> 2. The Oracle db is fairly big, so I'm kinda not in favor of[/color]


What does "big" actually mean in MB? How much of this data do you need?


Paul...

[color=blue]
> Sudhesh[/color]


--
plinehan y_a_h_o_o and d_o_t com
C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
Please do not top-post.

"XML avoids the fundamental question of what we should do,
by focusing entirely on how we should do it."

quote from http://www.metatorial.com

[David Penney]

Re: Linked Server to Oracle

Sudhesh,
my company has a product, MetaMatrix, that will link the two in exactly the
manner you require. It will also combine the Oracle & MS SQLServer schemas &
as a unified virtual database make them both together look like an instance
of your MSSQLServer database - then you can get all data together.

Regards
David Penney

http://www.metamatrix.com

"Sudhesh Nayak" <Sudhesh@mail.c om> wrote in message
news:8d85e256.0 312290944.26fc9 878@posting.goo gle.com...[color=blue]
> Hi,
> I have an Oracle (8.1) & a SQL Server 2000 database with
> Production data. There are situations when I need data from both the
> databases. My first choice was to link Oracle to SQL and run DTS
> overnight. But this would have a 1 day latency not to mention the time
> it would take.
>
> 1. Has any one tried real time access via Linked server to Oracle?
> How good is the performance?
>
> 2. The Oracle db is fairly big, so I'm kinda not in favor of
> copying the whole thing over into SQL overnight. Is there an easier
> way to just get only the changed records from Oracle?
>
> 3. Is there a better solution to this?
>
> 4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamically
> change the SQL that you pass it? e.g. can the query accept a
> parameter?
>
> Thanks in advance
> Sudhesh[/color]