Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Some Data from SServer to Oracle - Options ?

3 views
Skip to first unread message

klabu

unread,
Nov 10, 2006, 12:23:39 PM11/10/06
to
Looking for ideas:
Need some data (within Oracle/Lnux) from SQLServer(2K) on a CONTINUAL basis.

Does NOT need to be Real-Time...can be like Mat. View.

What are my options in impleneting this process ?

thanks
(and Yes I'm also asking the M$ guys also)


--
10gR2/Linux


DA Morgan

unread,
Nov 10, 2006, 6:41:01 PM11/10/06
to

How much data? Both companies offer solutions for this. From my
experience the Oracle one is more likely to work with newer versions
of Oracle.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

klabu

unread,
Nov 11, 2006, 2:00:38 AM11/11/06
to

"DA Morgan"

> How much data? Both companies offer solutions for this. From my
> experience the Oracle one is more likely to work with newer versions
> of Oracle.

Just a SELECT.....like < 1000 records

I read this
http://www.oracle.com/technology/products/oracle9i/datasheets/gateways/gateway_rel2_ds.html
Transparent Gateway is not an option (10gR2/Linux)

Generic Connectivity - not sure if I read it correctly - restriction :
Installed on the same machine as the database


Matthias Hoys

unread,
Nov 11, 2006, 5:57:16 AM11/11/06
to

"klabu" <noone_at_gmail_dot_com> wrote in message
news:12l9dcs...@corp.supernews.com...

You could install the Oracle client software on the SQL Server box, and then
create a DTS package to transfer data from SQL Server to the Oracle server.
You have to do the "mapping" of tables one by one though. Scheduling can be
done with the SQL Server agent, and you don't need any extra software
licenses to use this.

Matthias


Steve Howard

unread,
Nov 12, 2006, 9:25:22 PM11/12/06
to

To add another possible solution to the mix, we used the JVM in the
database to do this a few years ago (the SQL Server guys were kind of a
pain to get to do anything). Set autocommit to false on the connection
object and loop through the SQL Server resultset, manually committing
at the end to ensure they all succeed as a group...

<air code>

import java.sql.*;
public class foo {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection con =
DriverManager.getConnection("jdbc:microsoft:sqlserver://sql_server_host:1433","username","password");
con.autocommit(false);

PreparedStatement ps = con.prepareStatement("{ call
sql_server_proc() }");
//or below if a straight SELECT...
//PreparedStatement ps = con.prepareStatement("select column1 from
sql_server_table");
ps.setFetchSize(100); //or something higher to get more rows at
once...
ResultSet rs = ps.executeQuery();
PreparedStatement psInsert = con.prepareStatement("insert into your
table(column1) values(?)");
while (rs.next())
psInsert.setString(1,rs.getString(1));
psInsert.execute();
}
con.commit();
con.close();
}

</air code>

Regards,

Steve

Michael Austin

unread,
Nov 12, 2006, 10:23:41 PM11/12/06
to
klabu wrote:

> "DA Morgan"
>
>>How much data? Both companies offer solutions for this. From my
>>experience the Oracle one is more likely to work with newer versions
>>of Oracle.
>
>
> Just a SELECT.....like < 1000 records
>
> I read this
> http://www.oracle.com/technology/products/oracle9i/datasheets/gateways/gateway_rel2_ds.html
> Transparent Gateway is not an option (10gR2/Linux)

You could install the TG4MSSQL on the windows box and then use a database link
to pull it.


>
> Generic Connectivity - not sure if I read it correctly - restriction :
> Installed on the same machine as the database
>
>

This would use ODBC-type services.


--
Michael Austin.

0 new messages