Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Java/JDBC for dumping MS SqlServer rows to a text file ?

Re: Java/JDBC for dumping MS SqlServer rows to a text file ?

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 26 Jan 2003 01:31:12 -0800
Message-ID: <92eeeff0.0301260131.1f595dcb@posting.google.com>


alberto.dellera_at_bigfoot.com (Alberto Dell'Era) wrote in message news:<f4ed41c5.0301170324.60d52d87_at_posting.google.com>...
> Dear all,
>
> we are confronting ourselves with the daunting task of
> migrating some SqlServer tables into an Oracle 8.1.7 database;
> most of them are tiny tables but at least one of them
> is about 3,500,000 rows in size, about 1.5K for each row.
>
> We must migrate just the data; the definition of the tables
> has been already migrated, together with the stored procedures
> (that have been completely rewritten, since of course Oracle
> and SqlServer are so different, that a "mechanic" translation
> is neither possible nor desirable).
>
> Our task is complicated by us being located in Italy, where
> accented, non-ASCII characters are the norm. Our database has
> been of course already prepared with the correct character sets,
> and so on.
>
> We have evaluated different options (using the routines in sqlserver
> for direct migration; using the same routines for producing an
> intermediate dump file to be loaded by sql*loader; using oracle
> migration workbench; using the Oracle transparent gateways).
>
> Now, we are evaluating the possibility to write a small (trivial)
> Java/JDBC program to dump the SqlServer tables in a format easily
> recognizable by sql*loader (perhaps comma-separated columns, using an
> appropriate character set representation - probably UTF8).
>
> We would like to know whether anyone knows if an open-source, free
> Java/JDBC program is available that makes something similar.
> We need an open-source program because we need to address some
> low-level issues about the format of the data, and we would like to
> address them *before* dumping the data to the file.
> We need a free program for the simple reason that we can't buy
> anything, even if it costs less than $1, due to management
> decision (not a clever decision, but I can't do anything about it).
>
> As usual, many thanks to anyone that will answer our question,
> and many thanks also to anyone that has read so far.
>
> Kind Regards
> Alberto Dell'Era

Not sure if this is what you are looking for (Java/JDBC program to write delimited file) but just to give you an idea, here is a "simple, open-source and free" java sample which took me 5 minutes to write. This will work with Oracle.

import java.sql.*;
import java.io.*;

public class Test {

   public static void main (String args[])

      throws Exception {

      // Replace this with sqlserver compliant driver --v--
      DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());
      String url = "jdbc:oracle:oci8:@TEST92";      
      Connection conn =
         DriverManager.getConnection (url, "sys as sysdba",
"password");
      // Replace this with sqlserver compliant driver --^--

      Statement stmt = conn.createStatement ();
      ResultSet rset = 
         stmt.executeQuery ("select * from user_objects");
      // Open filesrtream
      FileWriter fw = 
         new FileWriter ("C:\\JavaDev\\test\\out.txt", true);
      String str = new String();
      while (rset.next()) {
         // Using pipe as delimiter. Can use any other delimiter
         str = rset.getString(1) + "|" + rset.getInt(3) + "|" +
rset.getDate(6);
         fw.write(str + "\r\n");
      }         
      fw.close();         
      rset.close();    
      stmt.close();    
      conn.close();

   }
}

However, I would suggest to use whatever in sqlserver that is equivalent to Oracle's UTL_FILE to write delimited file. BTW... migrating 3.5 million rows should not be a huge undertaking. Just see whatever works and go with it.

Regards
/Rauf Sarwar Received on Sun Jan 26 2003 - 03:31:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US