Java: Dumps Oracle database objects' DDL to text files (great for automatically committing changes to Git).

OracleDDLDumper.java

package [your package name here]

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 * <p/>
 * User: k
 * Date: 9/23/13
 * Time: 7:55 PM
 */
public class DBGIT {
    private Connection connection = null;
    // Connection properties for production database
    private final String proDbUrl = "jdbc:oracle:thin:@[IP Address]:[Port]:[SID]";
    private final String proDbUser = "[Username]";
    private final String proDbPass = "[Password]";

    // Connection properties for development database
    private final String devDbUrl = "jdbc:oracle:thin:@[IP Address]:[Port]:[SID]";
    private final String devDbUser = "[Username]";
    private final String devDbPass = "[Password]";

    // Connection properties to be used for run (see setDbProperties())
    private String dbUrl;
    private String dbUser;
    private String dbPass;

    private long timeStart;
    private long timeEnd;

    public static void main(String[] args) {
        new DBGIT();
    }
    public DBGIT() {
        this.timeStart = System.currentTimeMillis();
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            System.out.println("Unable to load Oracle Driver");
            e.printStackTrace();
            return;
        }

        setDbProperties(true);

        try {
            connection = DriverManager.getConnection(this.dbUrl, this.dbUser, this.dbPass);
        } catch (SQLException e) {
            System.out.println("SQLException when opening connection: " + e.getMessage());
            e.printStackTrace();
        }

        if (connection != null) {
            System.out.println("Connected to Oracle.");
            getDbObjects();
            try {
                connection.close();
            } catch (SQLException e) {
                System.out.println("SQLException when closing connection: " + e.getMessage());
                e.printStackTrace();
            }
        } else {
            System.out.println("NOT Connected to Oracle");
        }
        this.timeEnd = System.currentTimeMillis();

        System.out.println("Completed, total run time: " + (this.timeEnd - this.timeStart));
    }

    private void getDbObjects() {
        Statement statement = null;
        try {
            statement = connection.createStatement();
        } catch (SQLException e) {
            System.out.println("SQLException when creating statement: " + e.getMessage());
            e.printStackTrace();
        }

        ResultSet rsObjects = null;
        int o = 1;

        try {
            String sqlObjects = "SELECT OBJECT_NAME, OBJECT_TYPE " +
                    "FROM DBA_OBJECTS " +
                    "WHERE OWNER = 'VOAPPS' AND " +
                    "OBJECT_TYPE IN ('SEQUENCE','PROCEDURE','PACKAGE','TRIGGER','VIEW'," +
                    "'FUNCTION','TABLE','INDEX','TYPE', 'PACKAGE BODY', 'TYPE BODY', " +
                    "'JAVA CLASS', 'JAVA SOURCE', 'MATERIALIZED VIEW')";
            /*
            * Objects which fail GET_DDL:
            * JOB
            */
            rsObjects = statement.executeQuery(sqlObjects);
            while (rsObjects.next()) {
                String objName = rsObjects.getString("OBJECT_NAME");
                String objType = rsObjects.getString("OBJECT_TYPE");
                getObjectDdl(objName, objType);
                o++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rsObjects.close();
            } catch (Exception ignore) {}
        }
    }

    private void getObjectDdl(String objName, String objType) {
        PreparedStatement psObjectDdl = null;
        ResultSet rsObjectDdl = null;
        
        // Replace spaces in object type with underscore -- Prevents Oracle exception when processing 'Package Body'
        // or any other object name with a space.
        objType = objType.replace(" ", "_");

        try {
            String sqlObjDdl = "SELECT DBMS_METADATA.GET_DDL(?, ?) FROM DUAL";

            psObjectDdl = this.connection.prepareStatement(sqlObjDdl);
            psObjectDdl.setString(1, objType);
            psObjectDdl.setString(2, objName);
            rsObjectDdl = psObjectDdl.executeQuery();
            rsObjectDdl.next();
            String objDdl = rsObjectDdl.getString(1);
            writeDdl(objName, objType, objDdl);
        } catch (SQLException e) {
            System.out.println("Unable to get DDL for: " + e.getMessage());
            // e.printStackTrace();
        } finally {
            try {
                rsObjectDdl.close();
            } catch (Exception ignore) {}
            try {
                psObjectDdl.close();
            } catch (Exception ignore) {}
        }
    }
    private void writeDdl(String objName, String objType, String objDdl) {

        objName = objName.replace(" ", "_");
        objType = objType.replace(" ", "_");

        try {
            String filename = "/tmp/oracle_objects/" + objType + "_" + objName + ".sql";
            File file = new File(filename);
            FileWriter fileWriter = new FileWriter(file);
            BufferedWriter bufferedWriter = new BufferedWriter(fileWriter);
            bufferedWriter.write(objDdl);
            bufferedWriter.close();
        } catch (IOException e) {
            System.out.println("Unable to write: " + objName);
            e.printStackTrace();
        }

    }
    private void setDbProperties(boolean useProduction) {
        if (useProduction) {
            System.out.println("Using production paramters.");
            this.dbUrl = this.proDbUrl;
            this.dbUser = this.proDbUser;
            this.dbPass = this.proDbPass;
        } else {
            System.out.println("Using production paramters.");
            this.dbUrl = this.devDbUrl;
            this.dbUser = this.devDbUser;
            this.dbPass = this.devDbPass;
        }
    }
}