I am trying to load three separate tables from SQL Server 2012 into Oracle. I have established connections to SQL Server and Oracle in separate classes as seen below:
SQL Server connection:
public class TestSqlUtil {
public Connection getConnection() throws SQLException, IOException
{
String propsFile = "tasmania.properties";
Properties props = new Properties();
InputStream inputStream =
this.getClass().getClassLoader().getResourceAsStream(propsFile);
if (inputStream == null)
{
throw new FileNotFoundException("property file '" + propsFile
+ "' not found in the classpath");
}
props.load(inputStream);
String dblogin = props.getProperty("sqlfdb.LOGIN");
String dbpasswd = props.getProperty("sqlfdb.PASSWD");
String jdbcDrv = props.getProperty("sqlfdb.JDBCOCIDRV");
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection con = DriverManager.getConnection(jdbcDrv, dblogin, dbpasswd);
System.out.printf("successfull connection");
System.out.println();
return con;
}
public void cleanUp(Connection con, PreparedStatement ps, ResultSet rs)
throws SQLException
{
if (rs != null) rs.close();
if (ps != null) ps.close();
if (con != null) con.close();
}
public static void main(String a[]) throws SQLException, IOException
{
DaoUtil tasmaniaUtil = new DaoUtil();
tasmaniaUtil.getConnection();
}
}
Oracle connection:
public class DaoUtil
{
public Connection getConnection() throws SQLException, IOException
{
String propsFile = "tasmania.properties";
Properties props = new Properties();
InputStream inputStream =
this.getClass().getClassLoader().getResourceAsStream(propsFile);
if (inputStream == null)
{
throw new FileNotFoundException("property file '" + propsFile
+ "' not found in the classpath");
}
props.load(inputStream);
String dblogin = props.getProperty("intfdb.LOGIN");
String dbpasswd = props.getProperty("intfdb.PASSWD");
String jdbcDrv = props.getProperty("intfdb.JDBCOCIDRV");
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection con = DriverManager.getConnection(jdbcDrv, dblogin, dbpasswd);
System.out.printf("successfull connection");
System.out.println();
return con;
}
public void cleanUp(Connection con, PreparedStatement ps, ResultSet rs)
throws SQLException
{
if (rs != null) rs.close();
if (ps != null) ps.close();
if (con != null) con.close();
}
public static void main(String a[]) throws SQLException, IOException
{
DaoUtil tasmaniaUtil = new DaoUtil();
tasmaniaUtil.getConnection();
}
}
I have stored all of the login details within a properties file.
My first question is how would I combine these two classes to incorporate both connections? My second question would be how would do I use the select statement to grab all of the data from SQL Server and then the insert statement to insert into the Oracle Databases?
The tables in SQL Server and Oracle (from SQL Server to Oracle)
1. For MATERIAL_BATCH:
In SQL Server:
MATERIAL NUMBER|BATCH NUMBER|VENDOR BATCH NUMBER|VENDOR NUMBER|EXPIRATION DATE|
MODIFIED_DATETIME
In SQL Developer:
MATERIAL NUMBER|BATCH NUMBER|VENDOR BATCH NUMBER|VENDOR NUMBER|GOODS_SUPPLIER_NUMBER|
EXPIRATION DATE|INSTIME
2. For MATERIAL_MASTER:
In SQL Server:
PLANT|MATERIAL_NUMBER|MATERIAL_DESC|MODIFIED_DATETIME
In SQL Developer:
PLANT|MATERIAL_NUMBER|MATERIAL_DESC|PROFIT_CENTER_NAME|STATUS|INSTIME
3. For VENDOR:
In SQL Server:
VENDOR_NUMBER|VENDOR_NAME|MODIFIED_DATETIME
In SQL Developer:
VENDOR_NUMBER|VENDOR_NAME|VENDOR_LOCATION|INSTIME
There are some fields that are not in SQL Server that are in SQL Developer (Oracle). For those I will keep NULL.
