Converting from Oracle to Mysql

Below is the code you can cut paste and use for converting Oracle structures and data to mysql.
You need additional libraries

Classes12.zip oracle jdbc driver from oracle freely downloadable,
jakarta-oro.jar from apache.org and
mysql jdbc driver from mysql.com

Enjoy!

/*-----------------------------------------------------------------------------------------------------------------------------------------------------
* Program: oracle2mysql.java
* Author: Sib
* Date : 11/17/2004
* Purpose: Program to move specified schema from Oracle to mysql
* This is a quick and dirty method its not the best way but it works and gives you an idea
* of the steps involved. If you make modifications please advise so I can post them too
* You are free to use this as you please.
----------------------------------------------------------------------------------------------------------------------------------------------------*/
import java.sql.*;
import org.apache.oro.text.perl.Perl5Util;


public class oracle2mysql {

public static void main(String args[])
{
// create oracle connection
Connection OracleConnection = null;
// create mysql connection
Connection MysqlConnection = null;
String stmt = "";
int columnNumber=1;
//String columnDescription = null;
String dataType = null;
String dataScale = null;
//String ColumnString;
boolean isCreateOk;
try {
// Load the JDBC driver
DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver());
// Create a connection to the database
// replace local host with your database server name also change user scott and password tiger to reflect your
// This should be turned to some parameters or something

// You need classes.zip from oracle to establish the connection find it here otn.oracle.com
OracleConnection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");

// Load the JDBC driver
DriverManager.registerDriver( new org.gjt.mm.mysql.Driver()); // MySQL MM JDBC driver
// Create a connection to the database
// This database should already exist with the particular user and password setup too
MysqlConnection = DriverManager.getConnection("jdbc:mysql://localhost/testdatabase", "test", "test");

System.out.println("Created oracle and mysql connections ...");
//read oracle table structures
System.out.println("Reading Oracle table structures ...");
Statement sql=OracleConnection.createStatement();
Statement columnStmt=OracleConnection.createStatement();
Statement mySql=MysqlConnection.createStatement();

ResultSet rsColumns = null;
ResultSet rsTables = sql.executeQuery("select table_name from user_tables");

while (rsTables.next())
{
// map the column types
System.out.println("Mapping types...");
stmt = "select column_name, data_type, decode(nullable,'N','not null',' ') NULLABLE, data_length,nvl(data_scale,0) DATA_SCALE, DATA_PRECISION from cols where table_name='" + rsTables.getString("TABLE_NAME")+"'";
rsColumns = columnStmt.executeQuery(stmt);
columnNumber=1;

StringBuffer sbCreTable = new StringBuffer();
StringBuffer sbColumnList = new StringBuffer();

sbCreTable.append("CREATE TABLE ")
.append(rsTables.getString("TABLE_NAME"))
.append(" (");

while (rsColumns.next())
{
StringBuffer sbColumnDesc = new StringBuffer();
if (rsColumns.getString("DATA_TYPE").equals("NUMBER") && rsColumns.getInt("DATA_SCALE")<=0) dataType="INTEGER"; else if (rsColumns.getString("DATA_TYPE").equals("NUMBER") && rsColumns.getInt("DATA_SCALE")>0) dataType="FLOAT";
else if (rsColumns.getString("DATA_TYPE").equals("VARCHAR2"))
{
dataType="VARCHAR";
if (dataType=="VARCHAR" && rsColumns.getInt("DATA_LENGTH") > 255) dataType="BLOB";
}
else if (rsColumns.getString("DATA_TYPE").equals("LONG")) dataType="BLOB";
else if (rsColumns.getString("DATA_TYPE").equals("DATE")) dataType="DATETIME";
if (dataType=="DATETIME" || dataType=="BLOB")
sbColumnDesc.append(rsColumns.getString("COLUMN_NAME"))
.append(" ")
.append(dataType)
.append(" ")
.append(rsColumns.getString("NULLABLE"));
else
{
if (rsColumns.getInt("DATA_SCALE")==0)
{
sbColumnDesc.append(rsColumns.getString("COLUMN_NAME"))
.append(" ")
.append(dataType)
.append("(")
.append(rsColumns.getInt("DATA_LENGTH"))
.append(") ")
.append(rsColumns.getString("NULLABLE"));
}
else
sbColumnDesc.append(rsColumns.getString("COLUMN_NAME"))
.append(" ")
.append(dataType)
.append("(")
.append(rsColumns.getString("DATA_PRECISION"))
.append(",")
.append(rsColumns.getInt("DATA_SCALE"))
.append(") ")
.append(rsColumns.getString("NULLABLE"));
}
if (columnNumber == 1)
{
sbCreTable.append(sbColumnDesc);
// get column name
sbColumnList.append(rsColumns.getString("COLUMN_NAME"));
}
else
{
sbCreTable.append(", ")
.append(sbColumnDesc);
// get column name
sbColumnList.append(", ");
sbColumnList.append(rsColumns.getString("COLUMN_NAME"));
}

columnNumber++;
} // while (rsColumns.next())
sbCreTable.append(")");

// drop and create mysql table structure

try {
//isCreateOk =mySql.execute("DROP TABLE "+ rsTables.getString("TABLE_NAME"));
isCreateOk = mySql.execute(sbCreTable.toString());

}
catch (SQLException e)
{
System.out.println(e);
System.out.println(sbCreTable.toString());

}
insertRows(rsTables.getString("TABLE_NAME"),sbColumnList,OracleConnection,MysqlConnection);
// read oracle index structure
System.out.println("going for next table");
// create mysql index structure

} //while (rsTables.next())

rsTables.close();
//close connections

System.out.println("Closing connections ...");
OracleConnection.close();
MysqlConnection.close();
System.out.println("Connections closed - Goodbye!");

} catch (SQLException e) {
// some problem oocured at the database
System.out.println(e.getMessage());
}
}
public static void insertRows(String tableName, StringBuffer sbColumnList, Connection OracleConnection, Connection MysqlConnection)
{
try {
Statement oraStatement=OracleConnection.createStatement();
Statement mySqlStatement=MysqlConnection.createStatement();
Perl5Util regExpression = new Perl5Util();
StringBuffer sbQuery = new StringBuffer();
sbQuery.append("SELECT ")
.append(sbColumnList)
.append(" FROM ")
.append(tableName);

// Copy data
System.out.println("Copying " + tableName +" data from oracle ...");

try {
ResultSet rsRows = oraStatement.executeQuery(sbQuery.toString());
rsRows.setFetchSize(100);
ResultSetMetaData rsRowsMeta = rsRows.getMetaData();
while(rsRows.next())
{
StringBuffer sbInsertLine = new StringBuffer();
sbInsertLine.append("INSERT INTO ")
.append(tableName)
.append(" ( ")
.append(sbColumnList)
.append(" ) values (");
// Extract fields from the recordset
for(int i = 1;i <=rsRowsMeta.getColumnCount(); i++)
{
if (i==1)
{
//Determine whether to put quotation marks around the field
if(rsRows.getObject(i)!=null && (rsRowsMeta.getColumnType(i)==java.sql.Types.VARCHAR
|| rsRowsMeta.getColumnType(i)==java.sql.Types.CHAR
|| rsRowsMeta.getColumnType(i)==java.sql.Types.TIMESTAMP))
{
sbInsertLine.append("'")
// search object content for quotation marks and remove
.append(regExpression.substitute("s/\'//g",rsRows.getString(i)))
.append("'");
}
else
{
sbInsertLine.append(rsRows.getObject(i));
}

}
else
{
if(rsRows.getObject(i)!=null && (rsRowsMeta.getColumnType(i)==java.sql.Types.VARCHAR
|| rsRowsMeta.getColumnType(i)==java.sql.Types.CHAR
|| rsRowsMeta.getColumnType(i)==java.sql.Types.TIMESTAMP))
{
sbInsertLine.append(", ")
.append("'")
//.append(rsRows.getObject(i))
.append(regExpression.substitute("s/\'//g",rsRows.getString(i)))
.append("'");
}
else
{
sbInsertLine.append(", ")
.append(rsRows.getObject(i));
}
}
}
sbInsertLine.append(")");
// Execute the generated string
System.out.println(sbInsertLine.toString());

mySqlStatement.executeUpdate(sbInsertLine.toString());
mySqlStatement.executeUpdate("commit");
}
//close the resultset
rsRows.close();
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}
}