Oracle トリガー作成DDLを取得

Oracleのトリガーのソースを取得

以下手順にてトリガーのソース（DDL）をファイルに取得する事ができます。

手順１　SQLファイル（get_torigger_ddl.sql）を作成する。

まずは、適当なフォルダにget_torigger_ddl.sqlという名前のSQLファイルを作成します。記載する内容は以下の通り。(有効行は全てASCIIコードのはずなのでShift_JIS、utf-8どちらで作成しても動作するはずですが基本はクライアント環境にあわせてSQLファイルを作成してください）

SET LIN 20000
-- １行に収まらない内場合改行して表示
SET WRAP ON
SET PAGESIZE 0
SET LONG 400000
SET LONGCHUNKSIZE 400000
-- 問い合わせの結果レコード件数表示制御
SET FEEDBACK OFF
-- コマンド表示制御
SET ECHO OFF
-- コマンド結果表示制御
SET TERMOUT OFF

-- 行末の空白除去
SET TRIMS ON
-- バインド変数新旧表示除去
SET VERIFY OFF

--DDLの終わりにセミコロン
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE );


-- STORAGE属性を省略
--EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE );

-- --セグメント属性省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
-- 
-- 

-- スキーマ名省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA', FALSE);

--出力ファイル名変数定義
-- 『@』はスプールファイル名に使えないようなので、『_』に置き換える  2021/06/04
col spoolout_file_name new_value spool_file_name format a50
select replace('./TRIGGER_'||'&1'||'.'||'&2'||'.sql','@','_') spoolout_file_name 
from dual;

spool &spool_file_name

-- TABLE用DDLの取得
-- Oracleのバグなのか「EDITIONABLE」がついて出力されるので
-- これを無理やり除去する
select 
REGEXP_REPLACE (
dbms_metadata.get_ddl('TRIGGER',t.TRIGGER_NAME,t.OWNER) 
,' EDITIONABLE ', ' '
)
from DBA_TRIGGERS t
where t.OWNER= '&1' and t.TRIGGER_NAME = '&2'
;

spool off;

exit

手順２．実際にsqlplusを使いDDLを出力する。

SQL*plusをサイレントモード（-sオプション）で動作させ上記作成したSQLファイルを@で指定し、出力対象のテーブル名を指定します。
以下はDABADAスキーマが持つトリガー名「LOGON_TRIG」のDDLを出力する例です。パラメータとして渡すスキーマ名、トリガー名は大文字／小文字意識するのでご利用の際はご注意ください

sqlplus -s system/pasword@vm102/ora19.box @get_torigger_ddl.sql DABADA LOGON_TRIG

するとSQL*Plus実行フォルダに以下DDLファイルが作成されます。
TRIGGER_DABADA.LOGON_TRIG.sql

以下はWindows環境（PowerShell）環境での実行結果です

Dos窓であれば平気なのですが、PowerShellでは『’@get_torigger_ddl.sql’』とシングルクォーテーションで外部SQLファイルを囲む必要あります。

PS D:\temp> sqlplus -s system/ecgdev@vm102/ora19.box '@get_torigger_ddl.sql' DABADA LOGON_TRIG
PS D:\temp> ls -l TRIGGER_DABADA.LOGON_TRIG.sql


    ディレクトリ: D:\temp


Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----        2022/10/23     23:13           1112 TRIGGER_DABADA.LOGON_TRIG.sql


PS D:\temp> Get-Content TRIGGER_DABADA.LOGON_TRIG.sql

  CREATE OR REPLACE TRIGGER "LOGON_TRIG"
 AFTER LOGON ON DATABASE

DECLARE
 -- 自立型トランザクション宣言
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
  insert into dabada.MNT_EVENT
(
   EVENT_ID
  ,EVENT_TYPE
  ,EVENT_DATE
  ,CIL_OBJ_TYPE
  ,COL_OWNER
  ,COL_OBJ_NAME
  ,INST_ID
  ,SID
  ,AUDSID
  ,USERNAME
  ,SERVICE_NAME
  ,OSUSER
  ,MACHINE
  ,IP_ADDRESS
  ,PROGRAM
  ,NOTE
)
SELECT
    to_number(to_char(systimestamp,'yyyymmddhh24missff'))  EVENT_ID
  ,'LOGON' EVENT_TYPE
  ,systimestamp EVENT_DATE
  ,null CIL_OBJ_TYPE
  ,null COL_OWNER
  ,null COL_OBJ_NAME
  ,SYS_CONTEXT('USERENV', 'INSTANCE') INST_ID
  ,SYS_CONTEXT('USERENV', 'SID') sid
  ,SYS_CONTEXT('USERENV', 'SESSIONID') SESSIONID
  ,SYS_CONTEXT('USERENV', 'SESSION_USER') SESSION_USER
  ,SYS_CONTEXT('USERENV', 'SERVICE_NAME') SERVICE_NAME
  ,SYS_CONTEXT('USERENV', 'OS_USER') OS_USER
  ,SYS_CONTEXT('USERENV', 'HOST') HOST
  ,SYS_CONTEXT('USERENV', 'IP_ADDRESS') IP_ADDRESS
  ,SYS_CONTEXT('USERENV', 'MODULE') MODULE
  ,null note
  FROM DUAL
;
commit;

END ;
/
ALTER TRIGGER "LOGON_TRIG" ENABLE;

PS D:\temp>