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]

Sudhesh Nayak wrote:

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

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/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Sudhesh Nayak]

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 <da******@x.washington.edu> wrote in message news:<1072723371.798845@yasure>...

Sudhesh Nayak wrote:

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

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]

Sudhesh Nayak wrote:

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

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/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Serge Rielau]

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]

Serge Rielau wrote:

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

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/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Ellen K.]

One thing you have to watch out for, I don't think Oracle 8.1 supports
the SQL-92 join syntax, IOW you have to do the joins in the where
clause.

Another developer at my company is using DTS and I am not impressed
with the results. I prefer to code whatever ETL I want to do myself,
that way I have a lot more control and much better oversight... when
you use DTS if you want to see what it's doing you have to open up the
packages and click on all the little icons etc, and I don't think
there's any way to step through to debug either.

On 29 Dec 2003 09:44:22 -0800, Su*****@mail.com (Sudhesh Nayak) wrote:

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

[Serge Rielau]

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]

Serge Rielau wrote:

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

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/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Serge Rielau]

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]

Serge Rielau wrote:

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

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

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Paul]

Su*****@mail.com says...

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.

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.

2. The Oracle db is fairly big, so I'm kinda not in favor of

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

Sudhesh

--
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]

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" <Su*****@mail.com> wrote in message
news:8d**************************@posting.google.c om...

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

[Lyndon Hills]

On Sun, 1 Feb 2004 19:35:10 +0000 (UTC), "David Penney"
<an**@noone.com> wrote:

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" <Su*****@mail.com> wrote in message
news:8d**************************@posting.google. com...

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

I'm doings this. The main problem I had was wanting to relate data in
Oracle to that in SQL Server. It was horribly slow and we established
that what was happening was that MSSQL was going to pull the whole
table over from Oracle and then execute the query.

I solved using openquery. I created tables in SQLServer to hold what I
wanted from Oracle, populate using openquery and then deleted it.
Openquery performance is fine. You can use parameters, but you do it
by building up a string, containing the openquery statement and the
actual query and then you exec the string. This involves a nice game
of getting the right number of quotes!

A couple of other gotchas;

The oracle table names and columns have to be in capitals.
The oledb driver doesn't like columns defined as number, it wants them
to be NUMBER(12,0) or whatever. (The error is that the schema has
changed between parse and execution, or words to that effect).

Depending on your exact setup you may not find these problems, but we
did.

If you want a sample post a reply, as I'm not at work right now.

[David Penney]

Lyndon
thats one way to go - MetaMatrix does it a faster way without having the
overhead to write then read the data into the second database to do a join.
ts also quicker to deliver because the operatioin is specified in a
graphical modeling tool. Its also quicker to maintain than the soluution you
found. The product also transforms the data into a diffeerent datamodel &
can do reads & writes. Effectively the two databases are federated into a
Virtual Database with its own specific schema.

However if your solution is fast enough & a single limited case seems a good
solution for you, for more complex requirements its not optimal.

Regards,
Davd
"Lyndon Hills" <ly****@nospam.tenegi.com> wrote in message
news:ds********************************@4ax.com...

On Sun, 1 Feb 2004 19:35:10 +0000 (UTC), "David Penney"
<an**@noone.com> wrote:

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

Regards
David Penney
http://www.metamatrix.com

"Sudhesh Nayak" <Su*****@mail.com> wrote in message
news:8d**************************@posting.google. com...

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

I'm doings this. The main problem I had was wanting to relate data in
Oracle to that in SQL Server. It was horribly slow and we established
that what was happening was that MSSQL was going to pull the whole
table over from Oracle and then execute the query.

I solved using openquery. I created tables in SQLServer to hold what I
wanted from Oracle, populate using openquery and then deleted it.
Openquery performance is fine. You can use parameters, but you do it
by building up a string, containing the openquery statement and the
actual query and then you exec the string. This involves a nice game
of getting the right number of quotes!

A couple of other gotchas;

The oracle table names and columns have to be in capitals.
The oledb driver doesn't like columns defined as number, it wants them
to be NUMBER(12,0) or whatever. (The error is that the schema has
changed between parse and execution, or words to that effect).

Depending on your exact setup you may not find these problems, but we
did.

If you want a sample post a reply, as I'm not at work right now.