import java.io.*;
import java.sql.*;

public class GenerateSynonymScript {
    private static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String JDBC_URL = "jdbc:oracle:thin:@10.200.100.80:1521:MY_SCHEMA";
    private static final String JDBC_USER = "MY_SCHEMA";
    private static final String JDBC_PASSWD = "MY_PASSWORD";

    public static void main(String[] args) throws Exception {
        if (args.length < 1) {
            usage();
            System.exit(1);
        }

        String schemaName = args[0];

        Class.forName(JDBC_DRIVER);
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWD);
            generateSynonymScript(conn, schemaName);
        }
        finally {
            if (conn != null) conn.close();
        }
    }

    private static void generateSynonymScript(Connection conn, String schemaName) 
                        throws Exception {
        Statement stmt = conn.createStatement();
        
        // public synonyms
        ResultSet rs = stmt.executeQuery(
                            "SELECT * FROM dba_synonyms where TABLE_OWNER = '" 
                            + schemaName
                            + "' and OWNER = 'PUBLIC'"); 
        PrintWriter p = new PrintWriter(new FileWriter(schemaName + "_synonym.sql"));
        while (rs.next()) {
            String tableName = rs.getString("TABLE_NAME");
            p.println(String.format("CREATE OR REPLACE PUBLIC SYNONYM %s FOR %s.%s;",
                                    tableName,
                                    schemaName,
                                    tableName));
        }
        rs.close();

        // private synonyms
        rs = stmt.executeQuery(
                    "SELECT * from dba_synonyms where OWNER = '"
                    + schemaName
                    + "'");
        while (rs.next()) {
            String tableOwner = rs.getString("TABLE_OWNER");
            String tableName = rs.getString("TABLE_NAME");
            if (tableOwner != null) {
                p.println(String.format("CREATE OR REPLACE SYNONYM %s for %s.%s;",
                                        tableName,
                                        tableOwner,
                                        tableName));
            }
        }
        rs.close();

        p.close();
    }

    private static void usage() {
        System.out.println("java GenerateSynonymScript schemaName");
    }
}