以下の手順に従うと、ストアドパッケージをヘッダー用ファイルと、ボディー用ファイルを一気に出力する事ができます。個人的にも基本こちらの方法で出力したソースファイルをgitで管理するようにしています。今回はSQLファイルを利用していますが、SQLファイル自体にパラメータ渡しが可能で、ここでは２つのパラメータ（&1,&2)を渡しています。第一引数をスキーマ名、第二引数をパッケージ名としているので、いろいろ応用できると思います。
手順１ SQLファイル（get_PACKAGE.sql）を作成する。
まずは、適当なフォルダにget_PACKAGE.sqlという名前のSQLファイルを作成します。記載する内容は以下の通り。
SET LIN 20000
-- １行に収まらない内場合改行して表示
SET WRAP ON
SET PAGESIZE 0
SET LONG 400000
SET LONGCHUNKSIZE 400000
-- 問い合わせの結果レコード件数表示制御
SET FEEDBACK OFF
-- コマンド表示制御
SET ECHO OFF
-- コマンド結果表示制御
SET TERMOUT OFF
-- 行末の空白除去
SET TRIMS ON
-- バインド変数新旧表示除去
SET VERIFY OFF
--DDLの終わりにスラッシュ「/」
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE );
--出力ファイル名変数定義
col spoolout_file_name new_value spool_file_name format a50
--仕様部ファイル
select 'PACKAGE_'||'&1'||'.'||'&2'||'_'||'SPEC.sql' spoolout_file_name
from dual;
spool &spool_file_name
-- Oracleのバグなのか「EDITIONABLE」がついて出力されるので
-- これを無理やり除去する
SELECT REGEXP_REPLACE (
DBMS_METADATA.GET_DDL('PACKAGE_SPEC','&2','&1' )
,' EDITIONABLE ', ' '
)
from dual
;
-- パッケージに関わる権限
-- オブジェクトに対する権限が付与されている場合忘れがちなので注意必要
select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',t.TABLE_NAME, '&1' )
from dba_tab_privs t
where t.TABLE_NAME = '&2' and t.GRANTOR = '&1' and t.TYPE = 'PACKAGE'
;
spool off;
--仕様部ファイル
select 'PACKAGE_'||'&1'||'.'||'&2'||'_'||'BODY.sql' spoolout_file_name from dual;
spool &spool_file_name
SELECT REGEXP_REPLACE (DBMS_METADATA.GET_DDL('PACKAGE_BODY','&2','&1' )
, ' EDITIONABLE ', ' ')
from dual
;
-- パッケージに関わる権限
select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',t.TABLE_NAME, '&1' )
from dba_tab_privs t
where t.TABLE_NAME = '&2' and t.GRANTOR = '&1' and t.TYPE = 'PACKAGE'
;
spool off;
exit
SET LIN 20000
-- １行に収まらない内場合改行して表示
SET WRAP ON
SET PAGESIZE 0
SET LONG 400000
SET LONGCHUNKSIZE 400000
-- 問い合わせの結果レコード件数表示制御
SET FEEDBACK OFF
-- コマンド表示制御
SET ECHO OFF
-- コマンド結果表示制御
SET TERMOUT OFF
-- 行末の空白除去
SET TRIMS ON
-- バインド変数新旧表示除去
SET VERIFY OFF
--DDLの終わりにスラッシュ「/」
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE );
--出力ファイル名変数定義
col spoolout_file_name new_value spool_file_name format a50
--仕様部ファイル
select 'PACKAGE_'||'&1'||'.'||'&2'||'_'||'SPEC.sql' spoolout_file_name
from dual;
spool &spool_file_name
-- Oracleのバグなのか「EDITIONABLE」がついて出力されるので
-- これを無理やり除去する
SELECT REGEXP_REPLACE (
DBMS_METADATA.GET_DDL('PACKAGE_SPEC','&2','&1' )
,' EDITIONABLE ', ' '
)
from dual
;
-- パッケージに関わる権限
-- オブジェクトに対する権限が付与されている場合忘れがちなので注意必要
select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',t.TABLE_NAME, '&1' )
from dba_tab_privs t
where t.TABLE_NAME = '&2' and t.GRANTOR = '&1' and t.TYPE = 'PACKAGE'
;
spool off;
--仕様部ファイル
select 'PACKAGE_'||'&1'||'.'||'&2'||'_'||'BODY.sql' spoolout_file_name from dual;
spool &spool_file_name
SELECT REGEXP_REPLACE (DBMS_METADATA.GET_DDL('PACKAGE_BODY','&2','&1' )
, ' EDITIONABLE ', ' ')
from dual
;
-- パッケージに関わる権限
select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',t.TABLE_NAME, '&1' )
from dba_tab_privs t
where t.TABLE_NAME = '&2' and t.GRANTOR = '&1' and t.TYPE = 'PACKAGE'
;
spool off;
exit
SET LIN 20000
-- １行に収まらない内場合改行して表示
SET WRAP ON
SET PAGESIZE 0
SET LONG 400000
SET LONGCHUNKSIZE 400000
-- 問い合わせの結果レコード件数表示制御
SET FEEDBACK OFF
-- コマンド表示制御
SET ECHO OFF
-- コマンド結果表示制御
SET TERMOUT OFF
-- 行末の空白除去
SET TRIMS ON
-- バインド変数新旧表示除去
SET VERIFY OFF
--DDLの終わりにスラッシュ「/」
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE );
--出力ファイル名変数定義
col spoolout_file_name new_value spool_file_name format a50
--仕様部ファイル
select 'PACKAGE_'||'&1'||'.'||'&2'||'_'||'SPEC.sql' spoolout_file_name
from dual;
spool &spool_file_name
-- Oracleのバグなのか「EDITIONABLE」がついて出力されるので
-- これを無理やり除去する
SELECT REGEXP_REPLACE (
DBMS_METADATA.GET_DDL('PACKAGE_SPEC','&2','&1' )
,' EDITIONABLE ', ' '
)
from dual
;
-- パッケージに関わる権限
-- オブジェクトに対する権限が付与されている場合忘れがちなので注意必要
select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',t.TABLE_NAME, '&1' )
from dba_tab_privs t
where t.TABLE_NAME = '&2' and t.GRANTOR = '&1' and t.TYPE = 'PACKAGE'
;
spool off;
--仕様部ファイル
select 'PACKAGE_'||'&1'||'.'||'&2'||'_'||'BODY.sql' spoolout_file_name from dual;
spool &spool_file_name
SELECT REGEXP_REPLACE (DBMS_METADATA.GET_DDL('PACKAGE_BODY','&2','&1' )
, ' EDITIONABLE ', ' ')
from dual
;
-- パッケージに関わる権限
select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',t.TABLE_NAME, '&1' )
from dba_tab_privs t
where t.TABLE_NAME = '&2' and t.GRANTOR = '&1' and t.TYPE = 'PACKAGE'
;
spool off;
exit
手順２．実際にsqlplusを使ってDDLを出力する。
SQL*plusをサイレントモード（-sオプション）で動作させ上記作成したSQLファイルを@で指定し、出力対象のパッケージ名を指定します。
以下はSCOTTスキーマが持つパッケージ名「MY_PACKAGE」のDDLを出力する例
sqlplus -s system/pass@vm013/orau8 @get_PACKAGE.sql SCOTT MY_PACKAGE
sqlplus -s system/pass@vm013/orau8 @get_PACKAGE.sql SCOTT MY_PACKAGE
sqlplus -s system/pass@vm013/orau8 @get_PACKAGE.sql SCOTT MY_PACKAGE
上記の結果、SQLファイルを作成したフォルダに以下DDLが2ファイルに分けて作成される。
PACKAGE_SCOTT.PAC01_BODY.sql
PACKAGE_SCOTT.PAC01_SPEC.sql
おまけ：出力したDDLは以下の通り登録可能
SQL> @PACKAGE_SCOTT.PAC01_SPEC.sql
パッケージが作成されました。
SQL>
