はじめに

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

調査

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

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

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

Javaストアド方式

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

はじめてのJavaストアド

「PL/SQLで使う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のユーザー関数の呼び出しと同じように使える。おもしろい。

SELECT kasanf(2,3) FROM DUAL;

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

実装

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

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権限）で実行します。

CALL dbms_java.grant_permission( '<権限付与先スキーマ>', 'SYS:java.io.FilePermission',  '<<ALL FILES>>', 'execute' );

上記だと全てのファイルを対象とするため、セキュリティ上問題があるなら範囲をcmd.exeのみとします。

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

権限の確認方法

select * from DBA_JAVA_POLICY;

権限の削除方法

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

トリガー

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

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)を用意してログを出力するようにワンクッション置きます。

test.bat

cd /d %~dp0
start test2 %1

test2.bat

cd /d %~dp0
outputlog.exe %1
exit

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

DBMS_SCHEDULER方式

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

ジョブの実行権限

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

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

トリガー

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

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」を使うことで回避することができます。

補足

つまずいた部分で「Creating a dbms_scheduler.create_job with arguments - stackoverflow」で解決した部分です。

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

      number_of_arguments => 3,
      enabled    =>  TRUE,
      AUTO_DROP  => TRUE);

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

バッチ

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

test.bat

cd /d %~dp0
outputlog.exe %1

最後に

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

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

はじめに

調査

Javaストアド方式

はじめてのJavaストアド

実装

JAVAの実行権限

トリガー

バッチ

DBMS_SCHEDULER方式

ジョブの実行権限

トリガー

自律型トランザクション

補足

バッチ

最後に

参照