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

Advertisements

Sample JDBC program which connects to Oracle

Sample program that demonstrates using JDBC to connect to Oracle database.

Source code
$ cat TestConnection.java
import java.sql.*;

public class TestConnection {
public static void main (String args []) throws SQLException, InterruptedException
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
java.util.Properties props = new java.util.Properties();

// user/password@host:port:SID
Connection conn = DriverManager.getConnection
(“jdbc:oracle:thin:” + args[0] + “/” + args[1] + “@” + args[2] + “:” + args[3] + “:” + args[4], props);

Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(“select to_char(sysdate, ‘DD-MON-YYYY HH24:MI:SS’) from dual”);
while (rset.next()) {
String s = rset.getString(1);
System.out.println(“Date: ” + s);
}
stmt.close();
}
}

To compile and run
$ set CLASSPATH=.:/home/oracle/jar/ojdbc14.jar
$ javac TestConnection.java
$ java TestConnection username passowrd hostname port sid
Date: 14-OCT-2009 19:27:51

v$session osuser/program issue when sqldeveloper and oracle client installed

In v$session osuser, program are not set when oracle client 10.2.0.1 is used when SQLDeveloper and oracle client are installed on the same machine. This issue doesn’t occur when oracle client software (10.2.0.1) is not installed with SQLDeveloper, a workaround the issue is to update the following JDBC jars in $ORACLE_HOME. The jar files can be downloaded from the following URL http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html.

Following jars were replaced in oracle client directory (Note: you may want to backup the original jars before overwriting them):
ojdbc14.jar – $ORACLE_HOME/jdbc/lib
ojdbc14_g.jar – $ORACLE_HOME/jdbc/lib
ojdbc14dms.jar – $ORACLE_HOME/jdbc/lib
ojdbc14dms_g.jar – $ORACLE_HOME/jdbc/lib
orai18n.jar – $ORACLE_HOME/jlib

Oracle – JDBC v$session osuser/program issue

When an java app connects to oracle depending on the driver such as osuser, program in v$session will not be set (i.e. will be NULL) in Oracle. This issue is fixed in 10.2.0.4 oracle JDBC driver which can be downloaded from the following URL (http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html).

Another workaround this issue is to pass the value in java.util.Properties when establishing the connection.
(This workaround fix depends on the driver being used so it may not work in all situations).
….
java.util.Properties props = new java.util.Properties();

props.put(“v$session.program”, “testprogram”);
props.put(“v$session.osuser”,”Mike”);
Connection conn = DriverManager.getConnection(“jdbc:oracle:thin:scott/tiger@host:1521:TESTSID”, props);
….

In a separate window, during the execution of the program one can see the value of osuser and program is set.
SELECT osuser, program from V$session
OSUSER PROGRAM
————————- —————————
Mike testprogram

MySQL – JDBC connection example

—– Connect.java —-
—- To compile c:> javac Connect.java
—- Note: Make sure the CLASSPATH env is set. MySQL Connnector/J can be downloaded from http://dev.mysql.com/downloads/connector/
—- Example: CLASSPATH=mysql-connector-java-5.1.7\mysql-connector-java-5.1.7-bin.jar;.
—- To run c:> java Connect

— Connect.java code
import java.sql.*;

public class Connect {
public static void main(String argv[]) throws Exception {
Connection conn = null;
try {
String url = “jdbc:mysql://localhost:3306/test?user=root&password=root”;
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
conn = DriverManager.getConnection(url);
if ( conn != null )
System.out.println(“A connection to DB has been made”);

}
catch(Exception e)
{
throw e;
}
finally {
if ( conn != null )
conn.close();
conn = null;
}
}
}