Posted at

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

More than 1 year has passed since last update.


はじめに

自社開発のアプリケーションが使用するテーブルとその関連テーブルのログを出力したいよう改修要望がありました。そのログを参照して、別の外部アプリケーションがインフォメーション情報を出力するようにする。

ログを出力するタイミングはアプリケーションがテーブルにデータ登録した段階とのことでした。

それならアプリケーションを改修しなくても、データベースのトリガーを使ってバッチを実行すれば出来そうな気がしたので、調査することにしました。

アプリケーションの改修は出来るだけしたくないですしね。


調査

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


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

  • DBMS_SCHEDULERを使用してバッチを呼び出す方法

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

参照: PL/SQLからのログ出力方法を考える


Javaストアド方式

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


はじめてのJavaストアド

PL/SQLで使うJava」のサイトを参考にした。

Javaストアドを作成したら、呼び出す為のラッパーの作成も必要となる。

ラッパーは引数や戻り値の変更がなければ1回作ればよくて、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


最後に

結局、ログ出力する改修は無くなり別の方法となりましたが、いい勉強になりました。

簡単に検索すれば方法が見つかると思ったんですが、案外ないもんですね。

いろんなところから調べて組み合わせてやっと出来る感じです。


参照