CompareTables.java

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

public class CompareTables {

    private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
    private static final String USER = "username";
    private static final String PASS = "password";

    public static void main(String[] args) {
        try {
            Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);

            Map<String, ColumnDetail> sourceColumns = getTableColumns(conn, "SOURCE_TABLE");
            Map<String, ColumnDetail> archiveColumns = getTableColumns(conn, "ARCHIVE_TABLE");

            for (Map.Entry<String, ColumnDetail> entry : sourceColumns.entrySet()) {
                String columnName = entry.getKey();
                ColumnDetail sourceColumnDetail = entry.getValue();
                ColumnDetail archiveColumnDetail = archiveColumns.get(columnName);

                if (archiveColumnDetail == null) {
                    System.out.println("Column " + columnName + " missing in archive table.");
                } else {
                    if (!sourceColumnDetail.dataType.equals(archiveColumnDetail.dataType) ||
                        sourceColumnDetail.dataLength != archiveColumnDetail.dataLength) {
                        System.out.println("Mismatch found in column: " + columnName);
                        System.out.println("Source data type: " + sourceColumnDetail.dataType);
                        System.out.println("Archive data type: " + archiveColumnDetail.dataType);
                        System.out.println("Source data length: " + sourceColumnDetail.dataLength);
                        System.out.println("Archive data length: " + archiveColumnDetail.dataLength);
                        System.out.println();
                    }
                    // Remove the column from the archive table map
                    archiveColumns.remove(columnName);
                }
            }

            // If any columns remain in the archive table map, they are extra columns
            for (String columnName : archiveColumns.keySet()) {
                System.out.println("Extra column in archive table: " + columnName);
            }

            conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    private static Map<String, ColumnDetail> getTableColumns(Connection conn, String tableName) throws SQLException {
        Map<String, ColumnDetail> columns = new HashMap<>();

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT column_name, data_type, data_length FROM all_tab_cols WHERE table_name = '" + tableName + "' ORDER BY column_id");

        while (rs.next()) {
            columns.put(rs.getString("COLUMN_NAME"), new ColumnDetail(rs.getString("DATA_TYPE"), rs.getInt("DATA_LENGTH")));
        }

        rs.close();
        stmt.close();

        return columns;
    }

    public static class ColumnDetail {
        String dataType;
        int dataLength;

        public ColumnDetail(String dataType, int dataLength) {
            this.dataType = dataType;
            this.dataLength = dataLength;
        }
    }
}