平時(shí)常會(huì)使用Toad9.0導(dǎo)出package源碼文件（即pks文件），但Toad9.1似乎沒(méi)有這項(xiàng)功能了，為此我用Groovy寫了一個(gè)小程序。
源碼如下所示：
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?#############"
將這兩個(gè)文件放在同一目錄下，并修改conf.properties文件，即可使用。
最后，愿宋mm一路走好～附：
朝花夕拾——Groovy & Grails
posted on 2008-06-19 22:47
山風(fēng)小子 閱讀(3449)
評(píng)論(0) 編輯 收藏 所屬分類:
Groovy & Grails