Oracle9iのDBMS_METADATA.GET_DDLを使ってTABLE,INDEX,TRIGER,COMMENTを取得する

かなり久々、別に放置していた訳じゃないです。
仕事の都合で、現在の開発環境に定義されているDDLスクリプトを抜き出す必要があったので、DBMS_METADATAパッケージを使用してみた。

前に（言っても２年くらい前）DB2で同じようなことをやった時にはdb2lookupっていうコマンドが用意されていたので、かなり簡単にできたんだけど。。。
DBMS_METADATAパッケージって何ていうか癖があるような気がした。

私が欲しいのは

• 1.そのまま取得結果が実行できる(実行環境はsql*plusや他のサードパティ製ツール)
• 2.テーブル定義なら合わせてINDEXやCOMMENTの情報も欲しい
• 3.後々の保守も考えてテーブル単位にDDLをファイルに出力したい

で、結果としてこうなった

select
  DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME, OWNER)
from
  ALL_TABLES
where
  OWNER = :OWNER and
  TABLE_NAME = :NAME
union all
select
  DBMS_METADATA.GET_DEPENDENT_DDL('COMMENT', TABLE_NAME, OWNER)
from
  (select
     TABLE_NAME, OWNER
   from
     SYS.ALL_TAB_COMMENTS
   where
     OWNER = :OWNER and
     TABLE_NAME = :NAME and
     COMMENTS is not null
   union
   select
     TABLE_NAME, OWNER
   from
     ALL_COL_COMMENTS
   where
     OWNER = :OWNER and
     TABLE_NAME = :NAME and
     COMMENTS is not null)
union all
select
  DBMS_METADATA.GET_DEPENDENT_DDL('INDEX', TABLE_NAME, TABLE_OWNER)
from
  (select
     TABLE_NAME, TABLE_OWNER
   from
     ALL_INDEXES
   where
     TABLE_OWNER = :OWNER and
     TABLE_NAME = :NAME and
     INDEX_NAME not in (select CONSTRAINT_NAME from SYS.ALL_CONSTRAINTS
                        where TABLE_NAME = TABLE_NAME and
                              CONSTRAINT_TYPE = 'P') and
     UNIQUENESS != 'UNIQUE'
   group by TABLE_NAME, TABLE_OWNER)
union all
select
  DBMS_METADATA.GET_DDL('TRIGGER', TRIGGER_NAME, OWNER)
from
  ALL_TRIGGERS
where
  TABLE_OWNER = :OWNER and
  TABLE_NAME = :NAME
;

※ここでは１テーブルに対してのSQLにしてあります。 確かにこれで僕が欲しいスクリプトは取得可能なのだが、
私が求める要件１を満たさない。。。

セミコロンないしスラッシュがCOMMENT文に対して出力されない。。。

まあ、その問題もマニュアルを見ることで解消できた。
sql*plus上でこんな感じで実行すると私の欲しいスクリプトが出た。

set echo off
set termout off
set heading off
set pagesize 0
set linesize 10000
set trimout on
set trimspool on
set feedback off
set long 1048576
set longchunksize 1024

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', TRUE );

exec :NAME := 'テーブル名'
spool スプールログ名.sql
@↑のSQLファイル
spool off

肝だったのが、 DBMS_METADATA.SET_TRANSFORM_PARAMとlongchunksizeだった。 DBMS_METADATA.GET_DDLの実行結果はCLOBなので、longの設定はしていたのだけど、それだけだと特定の行サイズになると改行されてしまう。linesizeも設定してるんだけど改行されてしまう。。。
その状態を回避するのにlongchunksizeを使用した。 本来、CLOBに対してlong,longchunksizeはセットだと覚えておくと良いみたい。

セミコロンないしスラッシュがCOMMENT文に対して出力されない問題はDBMS_METADATA.SET_TRANSFORM_PARAMにSQLTERMINATORで幸せになれた。（しかし、何故にデフォルトでFalse設定なんだ？？）
とりあえず、これでまとめて欲しいものを出力することができた。