平时常会使用Toad9.0导出package源码文件（即pks文件），但Toad9.1似乎没有这项功能了，为此我用Groovy写了一个小程序。
源码如下所示：
conf.propertiesurl=jdbc:oracle:thin:USERNAME/PASSWORD@127.0.0.1:1521:orcl
schema=USERNAME
destination=./packages
ExportSrc.groovyimport java.sql.*;
import groovy.sql.*;
import oracle.jdbc.driver.OracleTypes;
Properties properties = new Properties();
properties.load(new FileInputStream("conf.properties"));
def url = properties.getProperty('url')
def schema = properties.getProperty('schema')
def destination = properties.getProperty('destination')
def destinationLocation = new File(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 rows for
SELECT 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 << row.name
}
}
def export = { schm, type, name ->
StringBuffer content = new StringBuffer()
sql.call("""
declare
lines SYS_REFCURSOR;
begin
open lines for
SELECT 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 << line.text
}
}
return content.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)
new File(destination + File.separator + name + '.pks').text = (packageSpecificationStr + '\n\n\n' +packageBodyStr).replaceAll(/(?<!(\r))\n/, '\r\n').trim() + '\r\n/'
}
println "############ ${names.size()} package(s) exported #############"
将这两个文件放在同一目录下，并修改conf.properties文件，即可使用。
最后，愿宋mm一路走好～附：
朝花夕拾——Groovy & Grails
posted on 2008-06-19 22:47
山风小子 阅读(3453)
评论(0) 编辑 收藏 所属分类:
Groovy & Grails