ビュー生成コマンド取得

-- Index --

・Top

・Softwares

▼親父の独り言集

▼Oracleデータベースに対する独り言集

・ご利用の前に
・CSVデータ出力バッチ作成
・SQLLoader実行バッチ作成(制御ファイルもね)
・主キー作成・削除コマンド取得
・実行中セッションKILLコマンド取得
・PL/SQLソース反映コマンド取得
・テーブル生成コマンド取得
→ビュー生成コマンド取得
・マテリアライズド・ビュー生成コマンド取得
・マテリアライズド・ビューログ生成コマンド取得
・順序(シーケンス)生成コマンド取得
・データベース・リンク生成コマンド取得
・シノニム生成コマンド取得
・サブクエリーのアクセス順指定
・グループ単位の通し番号とグループ内の連番

・VB.NETやC#に対する独り言集

・我が家の家電事情について

いきさつ

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 生成コマンドを実行時のエラー情報を格納


Copyright(c) 2014-2017 pakkin. All Rights Reserved.