Auto backup Oracle ddl

A java program based on DBMS_METADATA.GET_DDL is used to customize the auto backup on oracle  ddl. So the daily changes on table, package ddl will not be lost.

import java.io.BufferedWriter;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.OutputStreamWriter;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

public class BackupDB {

//Only the below needs to change based on the environment

private static final String URL = "jdbc:oracle:thin:@localhost:1521:xe";

private static final String USER = "hr";

private static final String PASSWORD = "hr";

private static final String[] TYPES = {"PACKAGE","TABLE"};

//do not include the schema prefix

private static final String NO_SCHEMA = "BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'EMIT_SCHEMA', false); END;";

//make it look pretty

private static final String PRETTY = "BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY', true); END;";

//don't include the physical properties

private static final String NO_PHYSICAL_PROP = "BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PHYSICAL_PROPERTIES', false); END;";

//don't include the segment attributes

private static final String NO_SEGMENT_PROP = "BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', false); END;";

//include a sql terminator for each statement

private static final String SQL_TERMINATOR = "BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true); END;";

public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {

long start = System.currentTimeMillis();

exportDDL("ddlExport.sql");

long end = System.currentTimeMillis();

System.out.printf("Done. Spent %d secs.",(end-start)/1000);

}

public static void exportDDL(String fileName) throws ClassNotFoundException, SQLException, IOException {

Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);

runCall(conn,NO_SCHEMA);

runCall(conn,PRETTY);

runCall(conn,NO_PHYSICAL_PROP);

runCall(conn,NO_SEGMENT_PROP);

runCall(conn,SQL_TERMINATOR);

List<String> resultList = new ArrayList<String>();

for(String type:TYPES){

List<String> ddlList = getDDLList(conn,type);

resultList.addAll(ddlList);

}

conn.close();

write(fileName, resultList);

}

private static List<String> getDDLList(Connection conn,String type) throws SQLException{

List<String> objectNameList = getObjectNameList(conn,type);

List<String> ddlList = new ArrayList<String>();

for(String objectName:objectNameList){

String sql = "select DBMS_METADATA.GET_DDL('"+type.toUpperCase()+"','"+objectName+"') from DUAL";

PreparedStatement preStatement = conn.prepareStatement(sql);

ResultSet result = preStatement.executeQuery();

while (result.next()) {

String ddl = result.getString(1);

ddlList.add(ddl);

}

result.close();

preStatement.close();

}

return ddlList;

}

private static List<String> getObjectNameList(Connection conn,String type) throws SQLException{

List<String> objectList = new ArrayList<String>();

String sql = "select object_name from user_objects where object_type = '"+type.toUpperCase()+"' order by 1";

PreparedStatement preStatement = conn.prepareStatement(sql);

ResultSet result = preStatement.executeQuery();

while (result.next()) {

String objectName = result.getString(1);

objectList.add(objectName);

}

result.close();

preStatement.close();

return objectList;

}

private static void runCall(Connection conn,String sql) throws SQLException{

CallableStatement cs = conn.prepareCall(sql);

cs.execute();

}

public static void write(String fileStr,Iterable<? extends Object> lines) throws IOException{

BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(fileStr)));

        for (Object line: lines) {

            writer.append(line.toString());

            writer.newLine();

        }

writer.close();

}

}