【Oracle】トリガーからWindowsバッチを実行する

# はじめに
自社開発のアプリケーションが使用するテーブルとその関連テーブルのログを出力したいよう改修要望がありました。そのログを参照して、別の外部アプリケーションがインフォメーション情報を出力するようにする。
ログを出力するタイミングはアプリケーションがテーブルにデータ登録した段階とのことでした。
それならアプリケーションを改修しなくても、データベースのトリガーを使ってバッチを実行すれば出来そうな気がしたので、調査することにしました。
アプリケーションの改修は出来るだけしたくないですしね。

# 調査
Windowsバッチを呼び出すには下記２通りがあります。

* Javaストアドを使用してバッチを呼び出す方法
* DBMS_SCHEDULERを使用してバッチを呼び出す方法

ログファイルを出力するだけなら、UTL_FILEパッケージやJavaストアド上でjava.io パッケージを使う方法がありますが、ネットワークドライブ先に保存したいとか何かと汎用性を考えるとWindowsバッチを呼び出した先で処理した方が何かと都合がよい。
参照： [PL/SQLからのログ出力方法を考える](http://www.seiji-tsubosaki.net/CHRONICLE/CONTENTS/contents01/contents_01.html)

# Javaストアド方式
OracleにはJava VMが実装されており、Javaプログラムをデータベース上で動作させることができます。これまでPL/SQLしか使ったことがなかったこともあり、ちょうどいい機会なので、Javaストアドごと学んでみました。

## はじめてのJavaストアド
「[PL/SQLで使うJava](http://www.ne.jp/asahi/hishidama/home/tech/oracle/java.html)」のサイトを参考にした。

Javaストアドを作成したら、呼び出す為のラッパーの作成も必要となる。
ラッパーは引数や戻り値の変更がなければ１回作ればよくて、Javaストアドの中身を後で書き換えても結果は反映される。

```java
CREATE OR REPLACE JAVA SOURCE NAMED java_test_src
AS
public class Test {
    public static int kasan(int a,int b){
        return a+b;
    }
}
/

CREATE OR REPLACE FUNCTION kasanf(a in number,b in number)
RETURN NUMBER
IS LANGUAGE java
NAME 'Test.kasan(int,int) return int'
;
/
```

PL/SQLのユーザー関数の呼び出しと同じように使える。おもしろい。

```SQL
SELECT kasanf(2,3) FROM DUAL;

KASANF(2,3)
-----------
          5
```

## 実装

Javaからコマンドを実行する Runtime.getRuntime().exec メソッドを利用し、引数を渡してWindowsバッチ(test.bat)を実行する。

```java
CREATE OR REPLACE JAVA SOURCE NAMED PLSQLExecByJava
AS
public class PLSQLExecByJava {
    public static String execByJava(
        String key
    ) {

        try {
            String cmd = "cmd /c start C:\\foo\\test.bat " + key;
            Runtime.getRuntime().exec(cmd);
            return "Success";

        } catch(Exception e) {
            return e.getMessage();
        }
    }
}
/

CREATE OR REPLACE FUNCTION FuncExec(key in VARCHAR2)
RETURN VARCHAR2
IS LANGUAGE java
NAME 'PLSQLExecByJava.execByJava(java.lang.String) return java.lang.String'
;
/
```
### JAVAの実行権限
Javaストアドプロシージャ内でコマンドの実行やファイルの入出力などを行う場合は、実行ユーザーに対して適切な権限を付与する必要があります。

実行権限の付与はDBMS_JAVAパッケージのGRANT_PERMISSIONプロシージャとなります。特権ユーザ（SYS, DBA権限）で実行します。

```bat
CALL dbms_java.grant_permission( '<権限付与先スキーマ>', 'SYS:java.io.FilePermission',  '<<ALL FILES>>', 'execute' );
```
上記だと全てのファイルを対象とするため、セキュリティ上問題があるなら範囲をcmd.exeのみとします。

```bat
call dbms_java.grant_permission('＜権限付与先スキーマ＞', 'SYS:java.io.FilePermission','C:\WINDOWS\system32\cmd.exe','execute');
```

権限の確認方法

```sql
select * from DBA_JAVA_POLICY;
```

権限の削除方法

```sql
begin
  DBMS_JAVA.disable_permission(<SEQ>);
  DBMS_JAVA.delete_permission(<SEQ>);
end;
```

## トリガー

FUGAテーブルのKeyNameカラムを引数にして、コマンドを実行します。
FuncExec内でバッチ(test.bat)が呼び出されます。

```sql:TRG_LOGOUTPUT
CREATE OR REPLACE TRIGGER "TEST"."TRG_LOGOUTPUT" 
AFTER INSERT
ON FUGA
FOR EACH ROW
DECLARE
  sResult VARCHAR2(200);
BEGIN
  IF INSERTING THEN
    SELECT FuncExec(:new.KeyName) INTO sResult FROM DUAL;
  END IF;
END;
```

## バッチ
トリガーからキーを引数にバッチ(test.bat)が呼び出されます。このキーをもらってログを出力します。しかし、この時点でSELECTしてもまだコミットされていないため対象のデータは取得できません。
その為、別のバッチ(test2.bat)を用意してログを出力するようにワンクッション置きます。

```bat:test.bat
cd /d %~dp0
start test2 %1
```

```bat:test2.bat
cd /d %~dp0
outputlog.exe %1
exit
```

この方法により、コミットされた状態になり対象のデータを取得できるようになります。また、ロールバックされた場合、対象のデータが見つからないのでログも出ません。

# DBMS_SCHEDULER方式
Javaストアドで出来るようになったので、DBMS_SCHEDULER方式にも挑戦してみました。

## ジョブの実行権限
DBMS_SCHEDULERを実行する権限を付与します。特権ユーザ（SYS, DBA権限）で実行します。

```bat
SQL> GRANT CREATE JOB TO ＜ユーザー名＞;
権限付与が成功しました。
SQL> GRANT CREATE REATE EXTERNAL JOB TO ＜ユーザー名＞;
権限付与が成功しました。
```

## トリガー
FUGAテーブルのKeyNameカラムを引数にして、コマンドを実行します。
その際に、自律型トランザクションの「autonomous_transaction」を指定しておきます。

```sql:TRG_LOGOUTPUT
CREATE OR REPLACE TRIGGER TRG_LOGOUTPUT 
BEFORE INSERT OR UPDATE
ON FUGA
FOR EACH ROW
DECLARE
  pragma autonomous_transaction;
BEGIN
  IF INSERTING THEN
    DBMS_SCHEDULER.CREATE_JOB (
      JOB_NAME   => 'job_test',
      JOB_TYPE   => 'EXECUTABLE',
      JOB_ACTION => 'C:\WINDOWS\system32\cmd.exe',
      number_of_arguments => 3,
      AUTO_DROP  => TRUE);

    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job_test',1, '/c');
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job_test',2, 'C:\foo\test.bat');
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job_test',3, :new.KeyName);
    DBMS_SCHEDULER.ENABLE('job_test');
  END IF;
END;
```
### 自律型トランザクション
DBMS_SCHEDULER.CREATE_JOBを呼び出すと、暗黙のコミットが発行されます。
通常はトリガーの内部ではコミットできないため失敗しますが、自律型トランザクションの「autonomous_transaction」を使うことで回避することができます。

*  [Call scheduler from a trigger](https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9529035800346449299)
*  [第56回 「自律型トランザクション](http://www.istudy.ne.jp/training/serial/plsql/056.html)
* [第57回 「自律型トランザクションとデータベーストリガー」](http://www.istudy.ne.jp/training/serial/plsql/057.html)

### 補足
つまずいた部分で「[Creating a dbms_scheduler.create_job with arguments - stackoverflow](https://stackoverflow.com/questions/29287440/creating-a-dbms-scheduler-create-job-with-arguments)」で解決した部分です。

下記のように「enabled=>TRUE」を入れたところ、「ORA-27457: 引数1(ジョブ"TEST.JOB_TEST")に値がありません」のエラーとなりました。

 ```sql
      number_of_arguments => 3,
      enabled    =>  TRUE,
      AUTO_DROP  => TRUE);
```
これはジョブは作成時にすぐに有効になるが、その時点で引数がまだ設定されていないため、エラーとなるのです。
「enabled=>TRUE」は、引数が無い場合はいいが、引数がある場合は引数設定後に有効にする必要があります。

## バッチ
トリガーからキーを引数にバッチ(test.bat)が呼び出されます。このキーをもらってログを出力します。
Javaストアドの時と違い、バッチ実行時に既にコミットされているため、ワンクッション置く必要がありません。ロールバックされた場合、対象のデータが見つからないのでログも出ません。

```bat:test.bat
cd /d %~dp0
outputlog.exe %1
```

# 最後に
結局、ログ出力する改修は無くなり別の方法となりましたが、いい勉強になりました。
簡単に検索すれば方法が見つかると思ったんですが、案外ないもんですね。
いろんなところから調べて組み合わせてやっと出来る感じです。

# 参照
* [OracleのトリガでJavaの処理を起動する](http://www005.upp.so-net.ne.jp/khayashi/ora_trig.html)
* [JavaストアドプロシージャでRuntimeクラスを使う - otn](http://otn.oracle.co.jp/forum/message.jspa?messageID=11004345)
* [PL/SQLから外部プログラムを実行 - otn](http://otn.oracle.co.jp/forum/thread.jspa?threadID=28002667&start=0&tstart=0)
* [PL/SQLからOSコマンドの実行 - otn](http://otn.oracle.co.jp/forum/message.jspa?messageID=35036759)
* [Revoke DBMS_JAVA permissions](http://kumarsatheesh.blogspot.jp/2012/09/revoke-dbmsjava-permissions.html)
* [10g ：トリガーからの外部プログラム呼び出しの可否について](http://www.databaseusers.com/article/7763879/10g+%EF%BC%9A%E3%83%88%E3%83%AA%E3%82%AC%E3%83%BC%E3%81%8B%E3%82%89%E3%81%AE%E5%A4%96%E9%83%A8%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%A0%E5%91%BC%E3%81%B3%E5%87%BA%E3%81%97%E3%81%AE%E5%8F%AF%E5%90%A6%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6)
* [[Oracle]ジョブをスケジューリングして定期実行する方法](http://skill-note.net/post-707/)