Many a times we are required to move data from one Database Management system to another. Unfortunately very little is done by the database vendors to make this an easier experience. I obviously understand that no vendor would want that people should move data from their product to another but that is something which customers deserve.
Well in the absence a good tool to do that we are left with only one option, write a script in our preferred language and move the data physically from one database to another. My preferred language is JAVA but you can use any other language. I’ll show how to connect to Oracle and MySQL and then we’ll move data from Oracle to MySQL. Piece of Cake. :P.
CONNECT TO ORACLE ##-> READ FROM ORACLE ##-> CONNECT TO MySQL ##-> WRITE INTO MySQL ##-> CLOSE CONNECTION
Connect to ORACLE (version 9i)
Prerequisite : Add the path of the following file in the CLASSPATH.
Class.forName(“oracle.jdbc.driver.OracleDriver”); //Loading the driver
String urlora=”jdbc:oracle:thin:@127.0.0.1:1521:home”; //URL for connecting to the database
//Creating Connection class object.
Statement stmtora=connora.createStatement(); //Statement class object to execute the query
ResultSet rsetora=stmtora.executeQuery(“QUERY inside double quotes”);
//Write the query here.
//ResultSet class object contains the retrieved data after the successful execution of the query.
So once you have the data retrieved from the Oracle database, you just need to write it into the MySQL database(Checkout the next part).
Connect to MySQL
Prerequisite : Download the drivers required to connect to the MySQL database from here. Extract it and add the path of the .jar file in your classpath.
stmtmy.executeUpdate(“Query inside quotes.”); //Write all the data read from Oracle into MySQL
And you are done!!! You can run this in loop and print the number of records of data moved from one database to other.
Note : The process can also be reverted to move data from MySQL to Oracle.