[Oracle] Execute Windows batch from trigger

Introduction

There was a request for repair to output the log of the table used by the application developed in-house and its related table. Refer to the log and have another external application output the information information. The timing to output the log was when the application registered the data in the table. In that case, I felt that it would be possible to execute a batch using a database trigger without modifying the application, so I decided to investigate. I don't want to modify the application as much as possible.

Investigation

There are two ways to call a Windows batch.

If you just want to output the log file, you can use the UTL_FILE package or the java.io package on Java stored, but considering the versatility of saving to a network drive destination or something, it is better to process it at the destination that called the Windows batch. Something is convenient. Reference: Consider how to output logs from PL / SQL

Java stored method

Oracle implements the Java VM, which allows Java programs to run on the database. I've only used PL / SQL before, so it's a good opportunity to learn Java stored.

First Java stored

I referred to the site of "Java used in PL / SQL".

Once you've created a Java stored, you'll also need to create a wrapper to call it. The wrapper only needs to be created once if there is no change in the argument or return value, and the result will be reflected even if the contents of the Java stored are rewritten later.

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'
;
/

It can be used in the same way as calling a PL / SQL user function. Interesting.

SELECT kasanf(2,3) FROM DUAL;

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

Implementation

Execute the command from Java Use the Runtime.getRuntime (). exec method and pass the argument to execute the Windows batch (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 execution authority

When executing commands or inputting / outputting files within a Java stored procedure, it is necessary to grant appropriate privileges to the executing user.

Granting execute permission is done by the GRANT_PERMISSION procedure in the DBMS_JAVA package. Execute as a privileged user (SYS, DBA authority).

CALL dbms_java.grant_permission( '<Privilege destination schema>', 'SYS:java.io.FilePermission',  '<<ALL FILES>>', 'execute' );

In the above case, all files are targeted, so if there is a security problem, the range is limited to cmd.exe.

call dbms_java.grant_permission('<Authorization destination schema>', 'SYS:java.io.FilePermission','C:\WINDOWS\system32\cmd.exe','execute');

How to check permissions

select * from DBA_JAVA_POLICY;

How to remove permissions

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

trigger

Execute the command with the KeyName column of the FUGA table as an argument. The batch (test.bat) is called within FuncExec.

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;

batch

The batch (test.bat) is called from the trigger with the key as an argument. Receive this key and output the log. However, even if you SELECT at this point, the target data cannot be obtained because it has not been committed yet. Therefore, prepare another batch (test2.bat) and place one cushion to output the log.

test.bat


cd /d %~dp0
start test2 %1

test2.bat


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

By this method, you will be in a committed state and you will be able to retrieve the target data. Also, if it is rolled back, no log will be output because the target data cannot be found.

DBMS_SCHEDULER method

Now that I can do it with Java stored, I also tried the DBMS_SCHEDULER method.

Job execution authority

Grants permission to run DBMS_SCHEDULER. Execute as a privileged user (SYS, DBA authority).

SQL>GRANT CREATE JOB TO <user name>;
The authorization was successful.
SQL>GRANT CREATE REATE EXTERNAL JOB TO <user name>;
The authorization was successful.

trigger

Execute the command with the KeyName column of the FUGA table as an argument. At that time, specify "autonomous_transaction" of the 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;

Autonomous transaction

Calling DBMS_SCHEDULER.CREATE_JOB issues an implicit commit. Normally, it fails because it cannot be committed inside the trigger, but it can be avoided by using the autonomous transaction "autonomous_transaction".

Supplement

The part that was solved by "Creating a dbms_scheduler.create_job with arguments --stackoverflow" is.

When I entered "enabled => TRUE" as shown below, I got an error "ORA-27457: Argument 1 (job" TEST.JOB_TEST ") has no value".

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

This will take effect immediately when the job is created, but it will result in an error because the arguments have not yet been set at that point. "Enabled => TRUE" is fine if there is no argument, but if there is an argument, it must be enabled after setting the argument.

batch

The batch (test.bat) is called from the trigger with the key as an argument. Receive this key and output the log. Unlike Java stored, it is already committed at batch execution, so there is no need to put a cushion. If it is rolled back, the target data cannot be found and no log will be output.

test.bat


cd /d %~dp0
outputlog.exe %1

Finally

In the end, the modification to output the log disappeared and it became another method, but it was a good study. I thought I could find a way by searching easily, but it's unexpected. It feels like I can finally find out from various places and combine them.

reference

Recommended Posts

[Oracle] Execute Windows batch from trigger
Execute non-Java instructions from Java