transfer data from Oracle to SQL 6.5

[Jean Chilson]

I need to create a remote connection to an Oracle database then execute a
series of statments to move the data into tables created with SQL 6.5. It
needs to be done using only T-SQL. I need help creating the remote login to
Oracle. Thanks in advance.

[Bruce Saffin]

Jean,
I have been working on a similar situation, and I haven't discovered how it
can be done using 'only T-SQL'. I have created an Oracle PL-SQL file on the SQL
Server machine, which issues a query to the Oracle database. Then, in SQL
Server, I use xp_cmdshell to run the PLUS33 executable and pass the connection
string, and the name of the PL-SQL file to run.

e.g. exec master..xp_cmdshell 'plus 33 -s scott/tiger@sid1 @c:\oracle_query.sql'

If you do this in a query window in SQL Server, you will see the data returned
from your Oracle query, along with some baggage messages from xp_cmdshell. From
within a stored procedure, you can insert the results of your xp_cmdshell into a
temp table. Then you can parse the table and separate your query results from
the other messages, and split the data into the columns you need.

If there's a simpler way to do this in SQL 6.5 I'd love to hear about it. In
SQL 7, it's a different matter altogether. Using DTS this is a pretty
straight-forward task.

...Bruce

[Scott Prugh]

You could install 7.0 on another machine and use DTS to move data from
Oracle to your 6.5 server. We were doing this before we upgraded to 7.0 and
it worked fine.

Regards,
Scott

Jean Chilson <jchi...@exec.gsinc.state.pa.us> wrote in message
news:u3KKQxLk#GA.265@cppssbbsa03...

[Suryanto]

How about if Oracle running on Sun machine ?

Scott Prugh <spr...@telution.com> wrote in article
<e0qT5JPk#GA.270@cppssbbsa03>...

[Scott Prugh]

Sure, you can use the Oracle OLEDB provider or the ODBC provider to move the
data.

Suryanto <sury...@commerce.net.id> wrote in message
news:01be9127$9363dce0$1ee2...@sur.gsk.net...