oracle 备份package,Groovy高效编程——导出Oracle的package源码

importjava.sql.*;importgroovy.sql.*;importoracle.jdbc.driver.OracleTypes;

Properties properties=newProperties();

properties.load(newFileInputStream("conf.properties"));

def url=properties.getProperty('url')

def schema=properties.getProperty('schema')

def destination=properties.getProperty('destination')

def destinationLocation=newFile(destination)if(!destinationLocation.exists()) {

destinationLocation.mkdirs()

}

Sql sql=Sql.newInstance(url,'oracle.jdbc.driver.OracleDriver');

def names=[]

sql.call("""declare

rows SYS_REFCURSOR;

begin

open rowsforSELECT DISTINCT NAME

FROM all_source

WHERE TYPE IN ('PACKAGE','PACKAGE BODY') AND owner='${schema}'ORDER BY NAME;

${Sql.resultSet OracleTypes.CURSOR} :=rows;

end;"""){ rows->rows.eachRow { row->names<

}

}

def export={ schm, type, name->StringBuffer content=newStringBuffer()

sql.call("""declare

lines SYS_REFCURSOR;

begin

open linesforSELECT text

FROM all_source

WHERE TYPE='${type}'AND owner='${schm}'AND name='${name}'ORDER BY TYPE, NAME, line;

${Sql.resultSet OracleTypes.CURSOR} :=lines;

end;"""){ lines->lines.eachRow { line->content<

}

}returncontent.toString().replaceFirst(/(?i)\bpackage\b/,'CREATE OR REPLACE PACKAGE')

}

names.each { name->def packageSpecificationStr=export(schema,'PACKAGE', name)

def packageBodyStr=export(schema,'PACKAGE BODY', name)newFile(destination+File.separator+name+'.pks').text=(packageSpecificationStr+'\n\n\n'+packageBodyStr).replaceAll(/(?

println"############ ${names.size()} package(s) exported #############"