Sample program that transfers data from MySQL to Oracle (CLOB)

Table structure in Oracle
SQL> desc scott.oracletable;
Name Null? Type
—————————————– ——– ———-
TRANSDATE NOT NULL DATE
SEQUENCE_NO NOT NULL NUMBER(6)
LOBDATA NOT NULL CLOB

Table structure in MySQL:
Name Type
—————————————– ——–
trandate Date
sequence_no smallint(6)
textfield longtext

— Code: TransferMySQLToOracle.java
import java.sql.*;
import java.io.*;
import java.lang.StringBuffer;
import java.util.Date;
import java.text.SimpleDateFormat;

public class TransferMySQLToOracle {
public static void main(String argv[]) throws Exception {
// DB connection for MySQL
Connection mysqlConn = null;
Statement mysqlStmt = null;
ResultSet mysqlRS = null;

// DB connection for Oracle
Connection oraConn = null;
PreparedStatement oraPstmt = null;

// parameters passed in start date and end date
String dtStart=argv[0];
String dtEnd=argv[1];

// print date
System.out.println(“: \n dtStart=” + dtStart);
System.out.println(“: \n dtEnd=” + dtEnd);

// set connection string for Oracle
String oradriverName = “oracle.jdbc.driver.OracleDriver”;
String oraurl = “jdbc:oracle:thin:@hostname:OraclePort:ORACLE_SID”;

// format to print timestamp
Date todaysDate;
SimpleDateFormat formatter = new SimpleDateFormat(“dd-MMM-yyyy HH:mm:ss”);
String formattedDate;

// default values
java.sql.Date dtTransactionDate = null;
int iSequenceNo = -1;

// print today date
todaysDate = new java.util.Date();
formattedDate = formatter.format(todaysDate);
System.out.println(“Start Time: ” + formattedDate);

try {
// connect to MySQL
String mysqlurl = “jdbc:mysql://mysqlserver:mysqlport/database?user=username&password=userpassword”;
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
mysqlConn = DriverManager.getConnection(mysqlurl);

// connect to Oracle
Class.forName(oradriverName);
oraConn = DriverManager.getConnection(oraurl, “scott”, “scottpassword”);
String oraInsert = “insert into scott.oracletable (trandate, sequence_no, lobdata) values(?, ?, ?)”;

if ( mysqlConn != null )
// Get a statement from the connection and prepare statement
// mysqlStmt = mysqlConn.createStatement();
mysqlStmt = mysqlConn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
mysqlStmt.setFetchSize(Integer.MIN_VALUE);
oraPstmt = oraConn.prepareStatement(oraInsert);

// date in yyyy-mm-dd format in mysql
// extract records from MySQL for the date specified
String msqlQry = “SELECT trandate, sequence_no, textfield FROM mysqltable” ;
msqlQry += ” Where trandate between ‘” + dtStart + “‘ and ‘” + dtEnd + “‘”;
mysqlRS = mysqlStmt.executeQuery(msqlQry) ;
todaysDate = new java.util.Date();

// print date for logging start time
formattedDate = formatter.format(todaysDate);
System.out.println(“Starting loop: ” + formattedDate);

// Loop through the result set
while( mysqlRS.next() ) {
// get and set trandate and sequence number
dtTransactionDate = mysqlRS.getDate(1);
iSequenceNo = mysqlRS.getInt(2);
oraPstmt.setDate(1, dtTransactionDate);
oraPstmt.setInt(2, iSequenceNo);

// loop through the lob stream and convert to string
Reader in = mysqlRS.getCharacterStream(3);
StringBuffer sb = new StringBuffer();
int buf = -1;
while((buf = in.read()) > -1) {
sb.append((char)buf);
}
in.close();

// ObjectInput in = new ObjectInputStream(mysqlRS.getCharacterStream(5));
oraPstmt.setString(3, sb.toString());

// insert record in oracle
oraPstmt.executeUpdate();
}
oraPstmt.close();
mysqlRS.close();
mysqlStmt.close();

// print end time
todaysDate = new java.util.Date();
formattedDate = formatter.format(todaysDate);
System.out.println(“End Time: ” + formattedDate);
}
catch(Exception e)
{
// print the record it failed on as date and sequence # was unique so one know the offending record in source
System.out.println(“Failed on Record:”);
System.out.println(“Transaction Date: ” + dtTransactionDate.toString());
System.out.println(“Sequence No: ” + iSequenceNo);

throw e;
}
finally {
if ( mysqlRS != null ) mysqlRS.close();
mysqlRS = null;
if ( mysqlStmt != null ) mysqlStmt.close();
mysqlStmt = null;
if ( mysqlConn != null ) mysqlConn.close();
mysqlConn = null;
if ( oraConn != null ) oraConn.close();
oraConn = null;
}
}
}

c:\> SET CLASSPATH=.;C:\driver\mysql-connector-java-5.0.8-bin.jar;C:\driver\ojdbc14.jar
c:> javac TransferMySQLToOracle.java

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s