Re: Some Data from SServer to Oracle - Options ?

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 12 Nov 2006 18:25:22 -0800
Message-ID: <1163384722.338004.48090@i42g2000cwa.googlegroups.com>

klabu wrote:
> 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

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 Received on Sun Nov 12 2006 - 20:25:22 CST