[TechDay] Oracle - ディクショナリからPLSQL PACAKGEのパッケージ毎のテキストファイルを生成

夏休みなどもありやや仕事もかさみBLOGをかなりサボってしまいました。m(_ _)m
久しぶりのBLOGはタイトルの通りOracleネタです。

Oracleに格納されているPL/SQLのコードを確認する際に皆さんはどうしていますか？1本づつプログラムを確認するだけなら Oracle SQL Developer などのIDEを使いますよね。

ところが大量にプログラムがあってテキストファイル化されていないときはどうしますか？
テキストファイルに対してgrepかけたりしたいですよね。

そんなときに便利なパッケージユーティリティを紹介します。

dbms_metadata.get_ddl です。

ディクショナリからDDLをリバースするパッケージですがこれはTABLEやINDEXはもちろんPACKAGE や PACKAGE BODY の DDL の生成も可能です。

以下に簡単に手順を紹介します。

１）SQL*Plus で dbms_metadata.get_ddl を実行して DDL を SPOOL します。
２）SPOOL したファイルに生成された DDL から PACKAGE 毎のSQLファイルを作成します。
　　※上記の２）はJavaプログラムで行いました。

SQL*Plus で dbms_metadata.get_ddl を実行して DDL を SPOOL

以下のSQLをSQL*Plusより実行して全パッケージのソースをテキストファイルにスプールします。対象は複数のパッケージでパッケージソースのサイズも大きいのでSQL*Plusの設定を予め変更します。dbms_metadata.get_ddl のオプションをデフォルトのまま行うとSQLのTerminatorが出力されないので SQLTERMINATOR オプションは true に設定します。

--*************************************
set head off
set pagesize 0
set long 10000000
set linesize 1000
set trims on
BEGIN
-- dbms_metadata.get_ddlユーティリティのオプション設定
-- terminator（/）を付加する
  DBMS_METADATA.SET_TRANSFORM_PARAM(
     TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM
     , NAME => 'SQLTERMINATOR'
     , VALUE => TRUE);
END;
/
spool all_package_source.txt
select dbms_metadata.get_ddl(object_type, object_name) from all_objects
      where owner = 'USER1'
      and object_type in ('PACKAGE','PACKAGE_BODY');
spool off
--*************************************

上記の例では、USER1 の PACKAGE と PACKAGE BODY すべての DDL を生成しています。コマンドを実行すると SPOOL コマンドで作成したファイル all_package_source.txt がカレントフォルダに出来上がります。

SPOOL したファイルに生成された DDL から PACKAGE 毎のSQLファイルを作成

生成されたSPOOLファイルには検索で該当したパッケージのDDL（PACKAGE、PACKAGE BODYの順）が多数含まれています。適当なプログラミング言語を使ってSPOOLファイルからPACKAGEごとにSQLファイルを生成するプログラムを作ってみましょう。

以下はJavaで作ったプログラムです。
SPOOL ファイルを引数にプログラムを実行すると シーケンス番号.sql というファイル名のSQLファイルが PACKAGE 数分作成されます。

C:\work>java mksqlfile all_package_source.txt

※PACKAGE名.sqlというファイル名になるプログラムも java.util.regex を使うと簡単に作成できるので必要なかたはご連絡ください。サンプルとして差し上げます！

BLOG全体につながりのないネタになりましたが。。一部の人にとっては便利かな。。

/***********************
Sample Java program
***********************/
import java.io.*;
//import java.util.regex.*;
public class mksqlfile {
    public static void main(String[] args) throws IOException {
        File inputFile = new File(args[0]);
        FileReader in = new FileReader(inputFile);
        BufferedReader b = new BufferedReader(in);
        String s;
        int i=1;
        int j=0;
        File outputFile = new File(i + ".sql");
        FileWriter out = new FileWriter(outputFile);
        String s1="/";
        while ((s = b.readLine()) != null) {
            if (s1.equals(s)) {
                j++;
            }
            if (j > 1) {
                out.write(s+"\n");
                out.close();
                outputFile = new File(i + ".sql");
                out = new FileWriter(outputFile);
                j=0;
            } else {
                out.write(s+"\n");
            }
            i++;
        }
        in.close();
        out.close();
        System.out.println("finish");
    }
}