oracle ddl extractor (based on JPA/hibernate)

While DBMS_METADATA.GET_DDL returns string in JDBC, it returns Clob in JPA/Hibernate, need to convert it to String.
The more realistic extractor would be:

import

java.io.BufferedReader;

import

java.io.BufferedWriter;

import

java.io.File;

import

java.io.FileOutputStream;

import

java.io.IOException;

import

java.io.OutputStreamWriter;

import

java.sql.Clob;

import

java.sql.SQLException;

import

java.text.DateFormat;

import

java.text.SimpleDateFormat;

import

java.util.Calendar;

import

java.util.List;

import

javax.persistence.EntityManager;

import

javax.persistence.EntityManagerFactory;

import

javax.persistence.Persistence;

public

class DDLExtractor

{

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

//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;";

private static final String ENV = "DIT2";

private static final DateFormat SDF = new SimpleDateFormat("yyyyMMdd");

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

{

long start = System.currentTimeMillis();

EntityManagerFactory emf = Persistence.createEntityManagerFactory(

ENV);

EntityManager em = emf.createEntityManager();

em.getTransaction().begin();

em.createNativeQuery(

NO_SCHEMA).executeUpdate();

em.createNativeQuery(

PRETTY).executeUpdate();

em.createNativeQuery(

NO_PHYSICAL_PROP).executeUpdate();

em.createNativeQuery(

NO_SEGMENT_PROP).executeUpdate();

em.createNativeQuery(

SQL_TERMINATOR).executeUpdate();

for(String type:TYPES){

createDDL(em,type);

}

em.getTransaction().commit();

em.close();

long end = System.currentTimeMillis();

System.

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

}

@SuppressWarnings("unchecked")

private static void createDDL(EntityManager em,String type) throws SQLException, IOException{

String sql =

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

List<String> objectNameList = (List<String>)em.createNativeQuery(sql).getResultList();

for(String objectName:objectNameList){

if(type.equalsIgnoreCase("PACKAGE")){

sql =

"select DBMS_METADATA.GET_DDL('PACKAGE_SPEC','"+objectName+"') from DUAL";

export(em,

"PACKAGE_SPEC",objectName);

export(em,

"PACKAGE_BODY",objectName);

}

else{

export(em,type,objectName);

}

}

}

private static void export(EntityManager em,String type,String objectName) throws IOException{

String sql =

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

Clob clob = (Clob)em.createNativeQuery(sql).getSingleResult();

String ddl = clobToString(clob);

File file = getFile(type,objectName);

write(file,ddl);

}

private static File getFile(String type,String objectName){

String folder =

"";

String fileName =

"";

if(type.equalsIgnoreCase("PACKAGE_SPEC")){

folder =

"package";

fileName =

"pks."+objectName.toLowerCase()+".sql";

}

else if(type.equalsIgnoreCase("PACKAGE_BODY")){

folder =

"package";

fileName =

"pkb."+objectName.toLowerCase()+".sql";

}

else if(type.equalsIgnoreCase("TABLE")){

folder =

"table";

fileName = objectName.toLowerCase()+

".ddl";

}

else if(type.equalsIgnoreCase("VIEW")){

folder =

"view";

fileName = objectName.toLowerCase()+

".ddl";

}

File parent =

new File(ENV+"_"+SDF.format(Calendar.getInstance().getTime()),folder);

if(!parent.exists()){

parent.mkdirs();

}

return new File(parent,fileName);

}

public static void write(File file,String content) throws IOException{

BufferedWriter writer =

new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file)));

writer.append(content);

writer.newLine();

writer.close();

}

public static String clobToString(Clob clob){

StringBuffer sb =

new StringBuffer();

if (clob == null) {

return sb.toString();

}

String line;

try {

BufferedReader br =

new BufferedReader(clob.getCharacterStream());

while ((line = br.readLine()) != null) {

sb.append(line).append(

"\r\n");

}

}

catch (Exception e) {

e.printStackTrace();

}

return sb.toString();

}

}