はじめに

とあるシステムを引き継いだのですがストアドプロシージャやパッケージがOracle内にしか登録されておらず、テーブルの利用状況などを確認するのに不便だったので、一括でファイル出力する処理を作ってみました。

WindowsコマンドとSQLしか利用していないのでおそらくどの環境でもできると思います。

(資料がきちんとあれば問題ないだろというツッコミはスルーします)

バッチファイル、SQLの作成

取得するSQLを作成する。SQLファイルとして保存する。

oracle_source_output.sql

SET pagesize 0
SET linesize 10000
SET feedback off
SET trimspool on
SET termout off

VARIABLE sourceType VARCHAR2(12)
VARIABLE sourceName VARCHAR2(30)

EXECUTE :sourceType := '&2'
EXECUTE :sourceName := '&3'

SPOOL &1

SELECT
    TEXT
FROM
    USER_SOURCE
WHERE
    TYPE = :sourceType
AND NAME = :sourceName
ORDER BY
    LINE
/
SPOOL OFF
/
EXIT
/

SQLを実行するバッチを作成する。バッチファイルとして作成する。

toracle_source_output.bat

@echo off
set outputMainFolder=C:\temp\
set sourceType=%1
set sourceName=%2
set extension=.SQL

rem '、空白を削除
set subFolder=%sourceType:"=%
set subFolder=%subFolder: =%

set outputFolder=%outputMainFolder%%subFolder%\

If not exist %outputFolder% mkdir %outputFolder%

set fileOutput=%outputFolder%%sourceName%%extension%

rem CSV出力処理
rem 引数1:ファイル出力先、引数2:タイプ、引数3:名称
sqlplus -s ID/Password@スキーマ名 @C:\temp\batch\oracle_source_output.sql %fileOutput% %sourceType% %sourceName%

作成したファイルを適当な位置に保存する。ここではC:\temp\batchに保存する。

取得用コマンドの作成

ストアドプロシージャのソースを取得したいDBに接続する。
user_sourceから取得したいタイプ、名前を取得する。この時、distinctを設定し、重複しないようにする。

コマンド取得用SQL

SELECT DISTINCT 
    'C:\temp\batch\oracle_source_output.bat' || ' "' || type || '" ' || name AS command 
FROM 
    user_source 
order by 
    command

コマンド実行

取得された結果をコマンドプロンプトで実行する。取得した結果がファイルとして作成される。

備考

作成されたファイルはCREATE文が付随していません。そのため、ストアドプロシージャを作成する際は適時CREATE文を入力する必要があります。　
PACKAGE BODYなど空白を含むものについては格納するフォルダは空白を削除するようにしています。
実行したことによりDBへの影響については自己責任でお願いします。あらかじめご了承ください。