いきさつ
DBMS_METADATA.GET_DDLでは、なかなか自分の欲しい形で整形出力されないので、自分で作成してみました。
準備するもの
まず、以下のスクリプトをコピーして、"viewexport.sql"というファイルを作成します。
このとき、以下の部分をメンテしてください。
| メンテ箇所 |
| in ('VIEW1','VIEW2'..) |
出力対象ビュー |
------------------------------------------------------------------
-- Copyright(c) 2015 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2015.10.30 作成
--
-- [実行方法]
-- 1)このテキストを、"viewexport.sql"として作成します。
-- 2)作成したフォルダ上で、コマンドプロンプトを起動し、以下のコマンドを実行します。
-- sqlplus -s user/password@servicename @viewexport.sql
-- 3)同一フォルダ上に、viewimport.sqlが作成されます。テーブルを生成したい場合、以下のコマンドを実行します。
-- sqlplus -s user/password@servicename @viewimport.sql
-- 4)コンパイル結果がviewimport.logとして出力されます。
------------------------------------------------------------------
set escape ?
set linesize 300
set pagesize 0
set term off
set head off
set feed off
set trim on
set trimspool on
set verify off
set long 40000
COLUMN CMDLINE FORMAT A300
COLUMN COL_SEP FORMAT A4
COLUMN COLUMN_NAME FORMAT A32
COLUMN DATA_TYPE FORMAT A14
COLUMN DATA_LEN FORMAT A12
COLUMN DATA_DEFAULT_T FORMAT A7
COLUMN NULLABLE FORMAT A8
COLUMN DESCEND FORMAT A4
COLUMN COMMENTS FORMAT A150
------------------------------------------------------------------
-- __viewexport1.sql(VIEW DDL)
------------------------------------------------------------------
spool __viewexport1.sql
prompt SPOOL VIEW/?&1..sql
prompt select 'CREATE OR REPLACE VIEW "' || viw.VIEW_NAME || '"(' as cmdline
prompt from USER_VIEWS viw
prompt where viw.VIEW_NAME = '?&?&1'
prompt /
prompt select decode(CLM.COLUMN_ID,1,' ',' ,') as COL_SEP
prompt , '"' || CLM.COLUMN_NAME || '"' as COLUMN_NAME
prompt , nvl2(CMN.COMMENTS,'-- ' || CMN.COMMENTS,null) as COMMENTS
prompt from USER_TAB_COLUMNS CLM
prompt , USER_COL_COMMENTS CMN
prompt where CLM.TABLE_NAME = CMN.TABLE_NAME(+)
prompt and CLM.COLUMN_NAME = CMN.COLUMN_NAME(+)
prompt and CLM.TABLE_NAME = '?&?&1'
prompt order by CLM.COLUMN_ID
prompt /
prompt prompt ) AS
prompt select TEXT as cmdline
prompt , '/' as endline
prompt from USER_VIEWS viw
prompt where viw.VIEW_NAME = '?&?&1'
prompt /
------------------------------------------------------------------
-- __viewexport1.sql(APPEND VIEW-COMMENT)
------------------------------------------------------------------
prompt select case when sqlline = 1 then
prompt 'COMMENT ON TABLE "?&?&1" IS ''' || tblc.COMMENTS || ''''
prompt else '/' end as cmdline
prompt from USER_TAB_COMMENTS tblc
prompt , (select 1 as sqlline from dual union all select 2 from dual )
prompt where tblc.TABLE_NAME = '?&?&1'
prompt and tblc.COMMENTS IS NOT NULL
prompt order by sqlline
prompt /
------------------------------------------------------------------
-- __viewexport1.sql(APPEND COLUMN-COMMENT)
------------------------------------------------------------------
prompt select case when sqlline = 1 then
prompt 'COMMENT ON COLUMN "?&?&1".' || rpad('"' || colc.COLUMN_NAME || '"',32) || ' IS ''' || colc.COMMENTS || ''''
prompt else '/' end as cmdline
prompt from USER_COL_COMMENTS colc
prompt , USER_TAB_COLUMNS col
prompt , (select 1 as sqlline from dual union all select 2 from dual )
prompt where colc.TABLE_NAME = col.TABLE_NAME
prompt and colc.COLUMN_NAME = col.COLUMN_NAME
prompt and colc.TABLE_NAME = '?&?&1'
prompt and colc.COMMENTS IS NOT NULL
prompt order by col.COLUMN_ID, sqlline
prompt /
prompt SPOOL OFF
spool off
------------------------------------------------------------------
-- __viewexport3.sql(RUN)
------------------------------------------------------------------
spool __viewexport3.sql
select '$ECHO Output-VIEW : ' || viw.VIEW_NAME || '
?@__viewexport1.sql ' || viw.VIEW_NAME as cmdline
from USER_VIEWS viw
where viw.VIEW_NAME in ('VIEW_1','VIEW_2'..)
order by 1
/
spool off
------------------------------------------------------------------
-- viewimport.sql
------------------------------------------------------------------
spool viewimport.sql
prompt SPOOL viewimport.log
prompt set linesize 300
prompt set pagesize 0
prompt set term off
prompt set head off
prompt set feed on
prompt set trim on
prompt set trimspool on
prompt set verify off
select 'PROMPT Create/Replace-VIEW : ' || viw.VIEW_NAME || '
?$ECHO Create/Replace-VIEW : ' || viw.VIEW_NAME || '
?@VIEW/' || viw.VIEW_NAME || '.sql' as cmdline
from USER_VIEWS viw
where viw.VIEW_NAME in ('VIEW_1','VIEW_2'..)
order by 1
/
prompt SPOOL OFF
prompt $ECHO 全ビューの生成が完了しました。
prompt EXIT
spool off
set escape off
$MKDIR VIEW >> __viewexport1.log 2>> __viewexport2.log
@__viewexport3.sql
$del __viewexport*.*
$echo スクリプトを生成しました。
exit
実行(DDL取得)
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
sqlplus -s user/password@servicename @viewexport.sql
| 実行時に生成されるサブフォルダ |
| VIEW |
VIEWのCREATE OR REPLACE文を生成 |
| 実行時に生成されるスクリプト |
| viewimport.sql |
生成されたCREATE OR REPLACE文を反映するコマンドを生成 |
実行(反映)
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
sqlplus -s user/password@servicename @viewimport.sql
| 実行時に生成されるログ |
| viewimport.log |
生成コマンドを実行時のエラー情報を格納 |