|
作者：山风小子
|
|
2008-06-21 15:33 |
|
平时常会使用Toad9.0导出package源码文件（即pks文件），但Toad9.1似乎没有这项功能了，为此我用Groovy写了一个小程序。
源码如下所示：
conf.properties
url=jdbc:oracle:thin:USERNAME/PASSWORD@127.0.0.1:1521:orcl
schema=USERNAME
destination=./packages
ExportSrc.groovy
import?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
|
|
最后更新于： 2008-06-21 15:33 |
