Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Java/JDBC for dumping MS SqlServer rows to a text file ?
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