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

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

前に(言っても2年くらい前)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
;
※ここでは1テーブルに対してのSQLにしてあります。 確かにこれで僕が欲しいスクリプトは取得可能なのだが、
私が求める要件1を満たさない。。。

セミコロンないしスラッシュが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設定なんだ??)
とりあえず、これでまとめて欲しいものを出力することができた。

Posted in ラベル: , | 0 コメント