DataCopyUtility.java

gistfile1.txt

import java.sql.*;

public class OracleTableCopyUtility {
    private static final String SOURCE_HOSTNAME = "source_hostname";
    private static final String TARGET_HOSTNAME = "target_hostname";
    private static final String SOURCE_SCHEMA = "source_schema";
    private static final String TARGET_SCHEMA = "target_schema";
    private static final String SOURCE_TABLE = "source_table";
    private static final String TARGET_TABLE = "target_table";
    private static final int NUM_ROWS_TO_COPY = 100;

    public static void main(String[] args) {
        try {
            // Connect to the source Oracle database
            Connection sourceConn = DriverManager.getConnection("jdbc:oracle:thin:@" + SOURCE_HOSTNAME + ":1521:xe", "username", "password");

            // Connect to the target Oracle database
            Connection targetConn = DriverManager.getConnection("jdbc:oracle:thin:@" + TARGET_HOSTNAME + ":1521:xe", "username", "password");

            // Get the column names from the source table
            DatabaseMetaData metaData = sourceConn.getMetaData();
            ResultSet columns = metaData.getColumns(null, SOURCE_SCHEMA, SOURCE_TABLE, null);

            StringBuilder columnNames = new StringBuilder();
            StringBuilder questionMarks = new StringBuilder();

            // Build the column names and question marks for the insert statement
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                columnNames.append(columnName).append(",");
                questionMarks.append("?,");
            }

            // Remove the trailing comma
            columnNames.deleteCharAt(columnNames.length() - 1);
            questionMarks.deleteCharAt(questionMarks.length() - 1);

            // Create the SQL statements
            String selectSql = "SELECT * FROM " + SOURCE_SCHEMA + "." + SOURCE_TABLE + " WHERE ROWNUM <= ?";
            String insertSql = "INSERT INTO " + TARGET_SCHEMA + "." + TARGET_TABLE + " (" + columnNames.toString() + ") VALUES (" + questionMarks.toString() + ")";

            // Prepare the statements
            PreparedStatement selectStmt = sourceConn.prepareStatement(selectSql);
            PreparedStatement insertStmt = targetConn.prepareStatement(insertSql);

            // Set the number of rows to copy
            selectStmt.setInt(1, NUM_ROWS_TO_COPY);

            // Execute the select statement
            ResultSet rs = selectStmt.executeQuery();

            // Iterate over the result set and copy the rows
            while (rs.next()) {
                // Set the values from the result set in the insert statement
                for (int i = 1; i <= columns.getMetaData().getColumnCount(); i++) {
                    insertStmt.setObject(i, rs.getObject(i));
                }

                // Execute the insert statement
                insertStmt.executeUpdate();
            }

            // Close the result set, statements, and connections
            rs.close();
            selectStmt.close();
            insertStmt.close();
            sourceConn.close();
            targetConn.close();

            System.out.println("Rows copied successfully.");
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}